Beast Mode計算例：日付変換

はじめに

Beast Modeで日付変換を作成する場合は、この記事の計算を参照してください。タイムゾーンの変換、週の番号の日付への変換、日付形式の変更、ダイナミック夏時間の有効化など、多数のサンプル計算が利用可能です。

GMTとタイムゾーン

CASE WHEN HOUR(CURRENT_TIMESTAMP()) < 8 THEN ADDDATE(CURRENT_DATE(), -1) ELSE CURRENT_DATE() END

8を、GMT時間との時差の数値に置き換えます。

日付や認識されない日付の変換

Beast Modeを使用すると、列の日付の形式を他の列に変換できます。次の例では、YYYY-MM-DDの形式に日付の形式を再設定します。

STR_TO_DATE(

CONCAT (

            YEAR (`Date`) , ‘ – ‘

            , DAY (`Date`) , ‘ – ‘

            , MONTH (`Date`)

)

, ‘ %Y-%m-%d ’

)

詳細は、「Beast Modeでの日付の形式指定文字列」を参照してください。

週の番号を日付に

Domoのデフォルトの「管理者」セキュリティロール、または「すべてのカンパニー設定を管理する」を有効にしたカスタムロールがある場合は、「チャートの日付形式を変更する」の説明に従って、[管理者設定] からすべてのカードの週の番号を日付に変換できます。「管理者」ではない場合、「すべてのカンパニー設定を管理する」のカスタムロールを持っていない場合、この変更を広範に行わない場合は、Beast Modeを使用してカード単位で週の番号を日付に変換する、次のような方法を使用します。

オプション1

SUBDATE(`Date`,WEEKDAY(`Date`)-1)

これは最もシンプルなバージョンであり、指定した週の日曜日の完全な日付を返します。そのため、週の番号が2015年の第1週の場合、計算によって「2015-01-04」が返されます（日曜日はこの年の1月4日であるため）。これは日付のように見えますが、Domoはそれを文字値と見なします。このBeast Modeを使用するすべてのカードは常に「毎週」にロックされるため、この値に時間粒度位セレクターを使用することはできません。

オプション2

DATE_FORMAT(SUBDATE(`Date`,WEEKDAY(`Date`)-1),'%m/%d/%Y')

これは前のオプションと同じものですが、日付の形式が異なります（「2015-01-04」の代わりに「01/04/2015」が返されます）。

オプション3

CONCAT('Week beg: ',DATE_FORMAT(SUBDATE(`Date`,WEEKDAY(`Date`)-1),'%b-%d'))

このバージョンでは、「Week beg:」というテキストが表示され、その週の日曜日の月と日のみが表示されます。例えば、「Week beg: Jan 04」のようになります。

オプション4

CONCAT(DATE_FORMAT(SUBDATE(`Date`,WEEKDAY(`Date`)-1),'%b %d'),' - ',DATE_FORMAT(SUBDATE(`Date`,WEEKDAY(`Date`)-7),'%b %d %Y'))

このバージョンでは、月と年の両方が値の対象になります。このバージョンでは、「Jan 01 - Jan 07 2015」が返されます。年は常に週末に関連付けられます。2014年の最後の週の場合、「Dec 28 - Jan 03 2015」と表示されます。

日付からタイムスタンプを削除する

月、日、年、時、分、秒を含む日付形式がある場合（例えば、21-Apr-16 1:53:46）、Beast Modeを使用して形式を変更できます。 

次のスクリプトは、必要に応じて日付のデータタイプを維持します。これにはDomoのデータタイプが日付タイプである必要があります。

DATE (`DateField`)

Domoデータタイプが文字列の場合は、変換できます。次の例は、その方法を示しています。

str_to_date('21-APR-16 1:53:46','%d-%b-%y’) 

未加工文字列フォーマットによっては、未加工値を正しく解釈するために、このBeast Modeの調整が必要になる場合があります。

ダイナミック夏時間

以下のダイナミックBeast Mode計算では、日付列を夏時間の日付と比較することができます。夏時間の特定範囲内に入っているかどうかによって、「yes」か「no」が返されます。

Beast Modeサンプル

「Date」は日付列です。

CASE

    WHEN `date`>= CASE

WHEN DAYOFWEEK(STR_TO_DATE(CONCAT(YEAR(curdate()), '-03-08'),'%Y-%m-%d')) = 0 THEN STR_TO_DATE(CONCAT(YEAR(curdate()), '-03-08'),'%Y-%m-%d')

WHEN DAYOFWEEK(STR_TO_DATE(CONCAT(YEAR(curdate()), '-03-09'),'%Y-%m-%d')) = 0 THEN STR_TO_DATE(CONCAT(YEAR(curdate()), '-03-09'),'%Y-%m-%d')

WHEN DAYOFWEEK(STR_TO_DATE(CONCAT(YEAR(curdate()), '-03-10'),'%Y-%m-%d')) = 0 THEN STR_TO_DATE(CONCAT(YEAR(curdate()), '-03-10'),'%Y-%m-%d')

WHEN DAYOFWEEK(STR_TO_DATE(CONCAT(YEAR(curdate()), '-03-11'),'%Y-%m-%d')) = 0 THEN STR_TO_DATE(CONCAT(YEAR(curdate()), '-03-11'),'%Y-%m-%d')

WHEN DAYOFWEEK(STR_TO_DATE(CONCAT(YEAR(curdate()), '-03-12'),'%Y-%m-%d')) = 0 THEN STR_TO_DATE(CONCAT(YEAR(curdate()), '-03-12'),'%Y-%m-%d')

WHEN DAYOFWEEK(STR_TO_DATE(CONCAT(YEAR(curdate()), '-03-13'),'%Y-%m-%d')) = 0 THEN STR_TO_DATE(CONCAT(YEAR(curdate()), '-03-13'),'%Y-%m-%d')

WHEN DAYOFWEEK(STR_TO_DATE(CONCAT(YEAR(curdate()), '-03-14'),'%Y-%m-%d')) = 0 THEN STR_TO_DATE(CONCAT(YEAR(curdate()), '-03-14'),'%Y-%m-%d')

END

 

    AND  `date`< CASE

WHEN DAYOFWEEK(STR_TO_DATE(CONCAT(YEAR(curdate()), '-11-01'),'%Y-%m-%d')) = 0 THEN STR_TO_DATE(CONCAT(YEAR(curdate()), '-11-01'),'%Y-%m-%d')

WHEN DAYOFWEEK(STR_TO_DATE(CONCAT(YEAR(curdate()), '-11-02'),'%Y-%m-%d')) = 0 THEN STR_TO_DATE(CONCAT(YEAR(curdate()), '-11-02'),'%Y-%m-%d')

WHEN DAYOFWEEK(STR_TO_DATE(CONCAT(YEAR(curdate()), '-11-03'),'%Y-%m-%d')) = 0 THEN STR_TO_DATE(CONCAT(YEAR(curdate()), '-11-03'),'%Y-%m-%d')

WHEN DAYOFWEEK(STR_TO_DATE(CONCAT(YEAR(curdate()), '-11-04'),'%Y-%m-%d')) = 0 THEN STR_TO_DATE(CONCAT(YEAR(curdate()), '-11-14'),'%Y-%m-%d')

WHEN DAYOFWEEK(STR_TO_DATE(CONCAT(YEAR(curdate()), '-11-05'),'%Y-%m-%d')) = 0 THEN STR_TO_DATE(CONCAT(YEAR(curdate()), '-11-05'),'%Y-%m-%d')

WHEN DAYOFWEEK(STR_TO_DATE(CONCAT(YEAR(curdate()), '-11-06'),'%Y-%m-%d')) = 0 THEN STR_TO_DATE(CONCAT(YEAR(curdate()), '-11-06'),'%Y-%m-%d')

WHEN DAYOFWEEK(STR_TO_DATE(CONCAT(YEAR(curdate()), '-11-07'),'%Y-%m-%d')) = 0 THEN STR_TO_DATE(CONCAT(YEAR(curdate()), '-11-07'),'%Y-%m-%d')

END

        THEN 'yes'

    ELSE 'no'

 

END

ダイナミック夏時間2

場合によっては、日付/時間列に時間を増減し、自分のタイムゾーンに合うように設定する必要がある場合があります。このダイナミッククエリは、夏時間に変更があった場合、必要な調整を自分の時間帯に対して行います。

以下の点にご注意ください。

• 「Date」の部分は、自分の日付/時間の列で置き換えます。

• 'from time zone'と'to time zone'は、会社のニーズに合わせて変更します。

• 長いタイムゾーン名を使用すると、CONVERT_TZ関数は自動的に夏時間を適用します。

CONVERT_TZ(`Date`,'from time zone','to time zone')

営業日による日付の差

2つの日付を平日/営業日の差として計算したい場合は、Beast ModeまたはSQL DataFlowでクエリを使って行うことができます。詳細は、「営業日による日付の差」を参照してください。

暦月・日

Date_Format(`MyDateColumn`,'%m/%d')

暦年

Year(`MyDateColumn`)

今年

case when Year(`MyDateColumn`) = year(current_date()) then 'True' else 'False' end

過去12か月

case
when
year(`MyDateColumn`) = year(current_date()) - 1
and month(current_date()) <= month(`MyDateColumn`)
then
'Yes'

when
month(`MyDateColumn`) <= month(current_date())
and year(`MyDateColumn`) = year(current_date())
then
'Yes'
else 'No'
end

過去12か月のフィルター

以下のコードを使って、今日の日付を基準にして、対応する日付値が前年の同じ月の最初の日の日付値以上（≧）の場合は「はい」の文字列を含む列を、そしてそうでない場合は「いいえ」の文字列を含む列を作成するような計算を作成します。

CASE WHEN `MyDateColumn` >= STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE() - 1), MONTH(CURRENT_DATE()), '01'), '%Y%m%d') THEN 'Yes' ELSE 'No' END

今月を除外する場合は、以下を追加します。AND `MyDateColumn` < DATE_FORMAT(CURRENT_DATE(), '%Y%m01')
CASE WHEN `MyDateColumn` >= STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE() - 1), MONTH(CURRENT_DATE()), '01'), '%Y%m%d')
AND `MyDateColumn` < DATE_FORMAT(CURRENT_DATE(), '%Y%m01')
THEN 'Yes' ELSE 'No' END

前年のフィルター

以下のコードを使い、今日の日付を基準にして、対応する日付値が前年の同じ日の日付値以上（≧）の場合は「はい」の文字列を含む列を、そうでない場合は「いいえ」の文字列を含む列を作成する計算を作成します。

CASE WHEN `MyDateColumn` >= STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE() - 1), MONTH(CURRENT_DATE()), DAY(CURRENT_DATE())), '%Y%m%d') THEN 'Yes' ELSE 'No' END

今日の日付を除外する場合は、以下を追加します。AND `MyDateColumn` < CURRENT_DATE()

CASE WHEN `MyDateColumn` >= STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE() - 1), MONTH(CURRENT_DATE()), DAY(CURRENT_DATE())), '%Y%m%d')
AND `MyDateColumn` < CURRENT_DATE()
THEN 'Yes' ELSE 'No' END

別の前年のフィルター

以下のコードを使って、月の数字の前に「0」を付けて2桁の月にするか決める計算を作成します。これは日付を設定するのに役立ちます。

CASE WHEN MONTH(CURRENT_DATE()) < 10 THEN CASE WHEN `MyDateColumn` >= STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE())-1, '0', MONTH(CURRENT_DATE()), '01'), '%Y%m%d') THEN 'Yes' ELSE 'No' END

連続した過去13か月

CASE WHEN PERIOD_DIFF(DATE_FORMAT(STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE()), MONTH(CURRENT_DATE())), '%Y%m%d'), '%Y%m'), DATE_FORMAT (`Month`, '%Y%m')) > 14
THEN 'No'
ELSE 'Yes'
END

今会計四半期

以下のコードを使い、今会計四半期で数値が下落するかどうかによって「はい」か「いいえ」を返す計算を作成します。

CASE WHEN `FiscalQuarter` = (CASE WHEN MONTH(CURRENT_DATE()) >= 2 AND MONTH(CURRENT_DATE()) <=4 then 1
ELSE CASE WHEN MONTH(CURRENT_DATE()) >= 5 AND MONTH(CURRENT_DATE()) <=7 then 2
ELSE CASE WHEN MONTH(CURRENT_DATE()) >= 8 AND MONTH(CURRENT_DATE()) <=10 then 3
ELSE 4 END
END
END)
THEN 'Yes' ELSE 'No'
END

今会計年度

以下のコードを使い、今年度中に数値が下落するかどうかによって「はい」か「いいえ」を返す計算を作成します。

CASE WHEN `FiscalYear` = (CASE WHEN MONTH(CURRENT_DATE())<2 THEN YEAR(CURRENT_DATE())-1 ELSE YEAR(CURRENT_DATE()) END) THEN 'Yes' ELSE 'No' END

会計カレンダー

以下のコードを使い、今年の日付と昨年の日付を同じ数値に位置づける「Adjusted Date」列を作成するスクリプトを作成します。

Date_Format(`Adjusted Date`, '%b %d')

その上で、日付にフィルターをかける計算を作成します。

CASE WHEN DateDiff(AddDate(Current_Date(), -1), `Adjusted Date`) < 30 AND DateDiff(Current_Date(), `Adjusted Date`) > 0 THEN 'Y' ELSE 'N' END

x日開始の週

以下のコードを使い、週ごとのデータの集計を出す計算を作成します。週の始めの日を変更するには、ｘの場所に1～6の数値を追加します。WEEKDAY()の曜日のインデックス方法はDAYOFWEEK()と異なることに注意が必要です。DATE_FORMAT()を使って、その他のフォーマットを指定することができます。

SUBDATE(`Date`, DAYOFWEEK(`Date`)+1)

曜日ごとのグラフの作成

次のビデオを見て、曜日ごとにデータを表示する方法を学んでください。

 

売掛金 / 買掛金エイジングバケット

以下のコードを使い、売掛金の計算を作成します。

CASE
when DATEDIFF(CURRENT_DATE(),`TxnDate`) < 31 then ' 30 Days'
when DATEDIFF(CURRENT_DATE(),`TxnDate`) < 61 then ' 60 Days'
when DATEDIFF(CURRENT_DATE(),`TxnDate`) < 91 then ' 90 Days'
when DATEDIFF(CURRENT_DATE(),`TxnDate`) < 121 then '120 Days'
when DATEDIFF(CURRENT_DATE(),`TxnDate`) > 120 then 'Over 120'
end