Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Below is what I need:
how to get cumulative sum,
for eg, I have fiscal year say, from 2009 - 2018.
My requirement is, when user select particular month of selected year, it should give me the sum from min date to max date selected,
2009: 2010:
Month, Amount Month, Amount
Apr, 2000 May, 30000
May, 3000 Jun, 1500000
Jun, 500 Aug, 20000
Jul, 4000 Sep, 130000
Consider above table for year 2009 and 2010,
when user selects Apr of 2009, my sum(amount) will be 2000, no doubt,
further, when user selects May of 2009, sum(amount) should be 2000+3000 = 5000,
similarly, when user selects Jul of 2009, sum(amount) should be 2000+3000+500+4000 = 9500
In addition to this, when user selects Jun of 2010, sum(amount) should be from Apr 2009 to Jun 2010,
which is, 2000+3000+500+4000+30000+1500000 = 1539500.
What I have achieved
I tried using,
= sum({<CalendarDate= {'>=01/04/2009 <=$(=max(CalendarDate))'}, fMonth=, FYear=>} Amount)
Using this expression if I make a selection of year 2010,I am getting total of 2009+2010, which is correct.
But, when I select May 2009, I get count for only that month, whereas I need to get Apr + May count of 2009.
Note : I have passed static min date, because when I passed $(=min(CalendarDate)) in place of 01/04/2009, expression was not able to read it.
Please help me out with this, if any.....
Thanks!
Perhaps like this: =sum({<CalendarDate= {"<=$(=max(CalendarDate))"}, fMonth=, FYear=>} Amount)
Perhaps like this: =sum({<CalendarDate= {"<=$(=max(CalendarDate))"}, fMonth=, FYear=>} Amount)
Thanks for your revert Gysbert,
Right after posting my question, I tried the same thing. It worked for me.
Thanks again!