Salesforceで動的な日付範囲を計算することが稀に必要になることがあるため、汎用的に使えるものをまとめておきます。
はじめに
レポートやダッシュボードで相対日付を利用して日付範囲を設定することがあるかと思います。
相対日付に存在する範囲であったり、検索条件で絞り込めるような要件であれば、簡単に実装ができます。
しかし、「半期で集計を取りたい」であったり「期間内に含まれるか否かで値を変更したい」というような場合、相対日付に存在しなかったり、検索条件で絞り込めないというような状況になります。
例えば、「2023年の1月から6月の集計を取りたい」と言われれば、直接的に
AND( 日付型 >= DATE( 2023 , 1 , 1 ) , 日付型 <= DATE( 2023 , 6 , 30 ) )
としてやれば良いですが、「常に当半期の集計を取りたい」というような場合は直接入力はメンテナンスが大変になります。
このような場合に対応できるようなテンプレートの数式を考えてみようと思います。
最終的な数式
先に出来上がった数式を示します。
AND(
範囲内を判定する日付 >= ADDMONTHS(
DATE( YEAR( 範囲を生成する日付 ) , MONTH( 範囲を生成する日付 ) , 1 ) ,
- MOD( 12 + MONTH( 範囲を生成する日付 ) - [基準月数] , [期間] ) + [期間] * [ズレ]
) ,
範囲内を判定する日付 < ADDMONTHS(
DATE( YEAR( 範囲を生成する日付 ) , MONTH( 範囲を生成する日付 ) , 1 ) ,
- MOD( 12 + MONTH( 範囲を生成する日付 ) - [基準月数] , [期間] ) + [期間] * ( [ズレ] + 1 )
)
)
ここで、各種パラメータは下記で設定します。
- 基準月数は含める基準月の数値（1月始まりであれば1、4月始まりであれば4など）
- 期間は含める期間の月数（月間であれば1、四半期であれば3、半期であれば6、年間や会計年度であれば12など）
- ズレは当〜からのズレ（当四半期であれば0、翌四半期であれば1など）
考え方のプロセス
考え方のプロセスとしては、下記の順序になります。
- 固定日の条件下で具体的に考える
- 日付を動的にする
- 範囲内の最小値の日付を求める
- 範囲内の最大値の日付を求める
- ズレを考慮する
- パラメータの出所を整理する
- 日付範囲で挟む
- 範囲の基準を整理する
固定日の条件下で具体例を考える
「今日が2023年8月29日で4月が会計年度の初月」という条件下で、任意の日付項目が当会計半期に含まれることを確認する計算を考えると、
AND( 日付項目 >= DATE( 2023 , 4 , 1 ) , 日付項目 <= DATE( 2023 , 9 , 30 ) )
となります。末日が面倒になることは目に見えているため、範囲を少し調整して、
AND( 日付項目 >= DATE( 2023 , 4 , 1 ) , 日付項目 < DATE( 2023 , 10 , 1 ) )
としておきます。
日付を動的にする
今日を動的に扱い、「4月が会計年度の初月」という条件下で、任意の日付項目が当会計半期に含まれることを確認する計算は、
- 今日が4月~9月に含まれる場合に今日から4月~9月の範囲を作り出す
- 今日が10月~3月に含まれる場合に今日から10月~3月の範囲を作り出す
を考える必要があります。このとき、範囲の考え方を少し変えてやると、
- $4 \le 今日の月数 \le 9 \implies 4月1日 \le 今日 < 10月1日 \implies 4月1日 \le 今日 < 4月1日 + 6ヶ月$
- $10 \le 今日の月数 \le 3 \implies 10月1日 \le 今日 < 4月1日 \implies 10月1日 \le 今日 < 10月1日 + 6ヶ月$
という表現が得られるため、最終的には範囲内の最小値の日付を今日の日付から算出することができれば、最大値は最小値の値に対して6ヶ月足せば良いことになります。（「6ヶ月」という数字が半期（6ヶ月間）から現れていることが見当付けられれば、後々の理解が進みやすいです。）
範囲内の最小値の日付を求める
簡単にするために対応表を作ります。
| 今日の月数 | 範囲内の最小値の月数 | 差分 |
|---|---|---|
| 4月 | 4月 | 0 |
| 5月 | 4月 | 1 |
| 6月 | 4月 | 2 |
| 7月 | 4月 | 3 |
| 8月 | 4月 | 4 |
| 9月 | 4月 | 5 |
| 10月 | 10月 | 0 |
| 11月 | 10月 | 1 |
| 12月 | 10月 | 2 |
| 1月 | 10月 | 3 |
| 2月 | 10月 | 4 |
| 3月 | 10月 | 5 |
対応表を見てみると、今月の月数の増加に対して、差分が一定の範囲で循環していることがわかります。ここで少し知識が必要になりますが、単一の循環の中で値が単調増加するような周期性のある値を求める際には剰余演算が使えるかをはじめに考えます。剰余演算を
差分 = x \bmod y
として、依存性の観点から$x$が今日の月数から算出できる必要があることを考慮すると、差分を範囲内に収めるためには$y = 6$が確定できます。（「6」という数字が半期（6ヶ月間）から現れていることが見当付けられれば、後々の理解が進みやすいです。）剰余演算を踏まえた対応表を作って$x$を割り当てると、
| 今日の月数 | 範囲内の最小値の月数 | 差分 | $x \bmod 6$ | $x$ |
|---|---|---|---|---|
| 4月 | 4月 | 0 | 0 | 12 |
| 5月 | 4月 | 1 | 1 | 1 |
| 6月 | 4月 | 2 | 2 | 2 |
| 7月 | 4月 | 3 | 3 | 3 |
| 8月 | 4月 | 4 | 4 | 4 |
| 9月 | 4月 | 5 | 5 | 5 |
| 10月 | 10月 | 0 | 0 | 6 |
| 11月 | 10月 | 1 | 1 | 7 |
| 12月 | 10月 | 2 | 2 | 8 |
| 1月 | 10月 | 3 | 3 | 9 |
| 2月 | 10月 | 4 | 4 | 10 |
| 3月 | 10月 | 5 | 5 | 11 |
と対応させることができます。ここから、範囲内の最小値の日付を求めるには
ADDMONTHS(
DATE( YEAR( TODAY() ) , MONTH( TODAY() ) , 1 ) ,
- MOD( MONTH( ADDMONTHS( TODAY() , - 4 ) ) , 6 )
)
とすることができます。ここで、より簡単にするために、
MOD( 12 + MONTH( TODAY() ) - 4 , 6 ) = MOD( MONTH( ADDMONTHS( TODAY() , - 4 ) ) , 6 )
の関係を使うと、
ADDMONTHS(
DATE( YEAR( TODAY() ) , MONTH( TODAY() ) , 1 ) ,
- MOD( 12 + MONTH( TODAY() ) - 4 , 6 )
)
となります。
範囲内の最大値の日付を求める
範囲内の最小値の日付を求めることができれば、範囲内の最大値の日付を求めることは簡単で、
ADDMONTHS(
DATE( YEAR( TODAY() ) , MONTH( TODAY() ) , 1 ) ,
- MOD( 12 + MONTH( TODAY() ) - 4 , 6 ) + 6
)
として求めることができます。
ズレを考慮する
これまでの範囲内の最小値の日付と範囲内の最大値の日付は「当会計半期」のみを考慮していましたが、実際には「翌会計半期」や「前会計半期」も計算できる方が良いため、範囲内の最小値の日付と範囲内の最大値の日付からのズレを含めた形を考えます。「翌会計半期」や「前会計半期」などの場合は、ズレの回数を$n$として$6n$の月数で並行移動させることで対応でき、
ADDMONTHS(
DATE( YEAR( TODAY() ) , MONTH( TODAY() ) , 1 ) ,
- MOD( 12 + MONTH( TODAY() ) - 4 , 6 ) + 6 * [ズレ]
)
ADDMONTHS(
DATE( YEAR( TODAY() ) , MONTH( TODAY() ) , 1 ) ,
- MOD( 12 + MONTH( TODAY() ) - 4 , 6 ) + 6 * ( [ズレ] + 1 )
)
となります。このズレは最小値と最大値で一致する必要はなく、「当会計半期 + 翌会計半期 + 翌々会計半期」などにも対応することができます。
パラメータの出所を整理する
範囲内の最小値の日付と範囲内の最大値の日付は会計年度の初月（4月）と半期（6ヶ月）からパラメータが設定されています。そのため、パラメータに任意性を与えることで会計年度の初月や範囲の期間を選べるようにすることができます。パラメータに任意性を与えると、範囲内の最小値の日付と範囲内の最大値の日付は、
ADDMONTHS(
DATE( YEAR( TODAY() ) , MONTH( TODAY() ) , 1 ) ,
- MOD( 12 + MONTH( TODAY() ) - [基準月数] , [期間] ) + [期間] * [ズレ]
)
ADDMONTHS(
DATE( YEAR( TODAY() ) , MONTH( TODAY() ) , 1 ) ,
- MOD( 12 + MONTH( TODAY() ) - [基準月数] , [期間] ) + [期間] * ( [ズレ] + 1 )
)
と表現することができます。
日付範囲で挟む
最初に立ち戻り、任意の日付項目が範囲内に収まることを判断する数式を記述すると、
AND(
日付項目 >= ADDMONTHS(
DATE( YEAR( TODAY() ) , MONTH( TODAY() ) , 1 ) ,
- MOD( 12 + MONTH( TODAY() ) - [基準月数] , [期間] ) + [期間] * [ズレ]
) ,
日付項目 < ADDMONTHS(
DATE( YEAR( TODAY() ) , MONTH( TODAY() ) , 1 ) ,
- MOD( 12 + MONTH( TODAY() ) - [基準月数] , [期間] ) + [期間] * ( [ズレ] + 1 )
)
)
となります。
範囲の基準を整理する
ここまで、日付範囲を今日から求めていましたが、今日という全ての日付を網羅する値に対応することができていれば、日付範囲を求める日付は今日でなくても良いため、
AND(
範囲内を判定する日付 >= ADDMONTHS(
DATE( YEAR( 範囲を生成する日付 ) , MONTH( 範囲を生成する日付 ) , 1 ) ,
- MOD( 12 + MONTH( 範囲を生成する日付 ) - [基準月数] , [期間] ) + [期間] * [ズレ]
) ,
範囲内を判定する日付 < ADDMONTHS(
DATE( YEAR( 範囲を生成する日付 ) , MONTH( 範囲を生成する日付 ) , 1 ) ,
- MOD( 12 + MONTH( 範囲を生成する日付 ) - [基準月数] , [期間] ) + [期間] * ( [ズレ] + 1 )
)
)
としても問題ありません。
まとめ
最後には色々と複雑な考え方で辿り着きましたが、もっとシンプルな考え方がないかと未だに模索中です。（年間の範囲に関しては年数で計算が可能なため、もっとシンプルになります。）
一応は、パラメータの使い方次第で柔軟な範囲に対応できるので、使い所はあるかなとは感じています。