Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi , Experts,
Greetings of the day,
Please help to resolve below problem for desired output.
My Data are in below two tables.
Table-1
FY | Year | Month |
2017-18 | 2017 | Apr |
2017-18 | 2017 | May |
2017-18 | 2017 | Jun |
2017-18 | 2017 | Jul |
2017-18 | 2017 | Aug |
2017-18 | 2017 | Sep |
2017-18 | 2017 | Oct |
2017-18 | 2017 | Nov |
2017-18 | 2017 | Dec |
2017-18 | 2018 | Jan |
2017-18 | 2018 | Feb |
2017-18 | 2018 | Mar |
2018-19 | 2018 | Apr |
2018-19 | 2018 | May |
Table-2
Year | Month | Input_qt |
2017 | Apr | 772.17 |
2017 | May | 851.96 |
2017 | Jun | 755.04 |
2017 | Jul | 740.49 |
2017 | Aug | 775.89 |
2017 | Sep | 750.70 |
2017 | Oct | 743.12 |
2017 | Nov | 596.68 |
2017 | Dec | 564.18 |
2018 | Jan | 573.49 |
2018 | Feb | 574.56 |
2018 | Mar | 768.54 |
2018 | Apr | 751.73 |
2018 | May | 831.12 |
I want to output as below:
Cumulative Input_qt of last three months on selecting FY 18-19 table linked by synth key
March-1916.59 (i.e.573.49+574.56+768.54)
April-2094.83 (i.e.574.56+768.54+751.73)
May-2351.39 (i.e.768.54+751.73+831.12)
When applying =RangeSum(Above(sum(Input_qt))) unable to fetch cumulative sum of last FY march and cumulative starts from April onwards as below
Month | =RangeSum(sum(Input_qt)) |
Apr | 751.733 |
May | 1582.849 |
Jun | 2417.114 |
Jul | 3223.784 |
Aug | 4040.182 |
Sep | 4819.098 |
Oct | 5592.798 |
Nov | 6244.658 |
Dec | 6831.218 |
Jan | 7427.199 |
Feb | 8010.219 |
Mar | 8784.003 |
Thanks and Regds to all forum members
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:
below link will help to calculate cumulative 3 months data.