Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
yes there are smart solutions in community giving you awesome expressions that does cumulative. however, i would solve this by data model. i would associate a month to the 3 months that represent its cumulative months. so when the user selects Jan 2023, it is associated to Jan, Feb, Mar 2023. and the expression will be simpler and faster.
data:
NoConcatenate
load * inline [
DateKey,Year,Month, Input_qt
4/1/2017, 2017,Apr,772.17
5/1/2017, 2017,May,851.96
6/1/2017, 2017,Jun,755.04
7/1/2017, 2017,Jul,740.49
8/1/2017, 2017,Aug,775.89
9/1/2017, 2017,Sep,750.70
10/1/2017, 2017,Oct,743.12
11/1/2017, 2017,Nov,596.68
12/1/2017, 2017,Dec,564.18
1/1/2018, 2018,Jan,573.49
2/1/2018, 2018,Feb,574.56
3/1/2018, 2018,Mar,768.54
4/1/2018, 2018,Apr,751.73
5/1/2018, 2018,May,831.12
];
calendar:
load DateKey as Date, Year, Month Resident data;
NoConcatenate
Bridge:
load Date, Date as DateKey, 'ACTUAL' as DateType Resident calendar;
NoConcatenate
tmp:
load Date Resident calendar;
Inner join (tmp)
load Date as DateKey Resident tmp;
concatenate (Bridge)
load Date, DateKey, 'CUMULATIVE' as DateType Resident tmp
where DateKey<=AddMonths(Date,2) and DateKey>=Date;
drop table tmp;
drop fields Year, Month from data;
this gives you an idea of the association of the alues when a user selects a few dates:
this is the chart with the expressions:
even when you choose non contiguous dates, you should still get the right values:
