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')
例
CONVERT_TZ(`Date`, '+00:00', '+10:00')
CONVERT_TZ(`Date`,'UTC','American/Chicago')
これは、CURRENT_TIMESTAMP()、CURRENT_DATE()、CURDATE()などの現在の日付関数を使用する場合に大変便利です。これらの関数はUTCで現在の日時を返すため、同じCONVERT_TZロジックを適用すれば、手動で時間を加減することなく、タイムゾーンの現在の時刻を取得できます。
DATE(CONVERT_TZ(CURRENT_TIMESTAMP(), 'UTC', 'America/Chicago'))
営業日による日付の差
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日開始の週
以下のコードを使い、週ごとのデータの集計を出す計算を作成します。週の始めの日を変更するには、xの場所に1~6の数値を追加します。WEEKDAY()の曜日のインデックス方法はDAYOFWEEK()と異なることに注意が必要です。DATE_FORMAT()を使って、その他のフォーマットを指定することができます。
SUBDATE(`Date`, DAYOFWEEK(`Date`)+1)
曜日ごとのグラフの作成
次のビデオを見て、曜日ごとにデータを表示する方法を学んでください。
注記:この製品トレーニングビデオは、Domoのお客様とクライアント様のみを対象としています。
売掛金 / 買掛金エイジングバケット
以下のコードを使い、売掛金の計算を作成します。
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