Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qv'ers
I have a problem with showing a sum of the last X months in a straight table, where x i = 3.
I use the following expression to get the sum of the month;
sum(ReturnedItems)
in a straight table with Year and Month as dimensions (as seen in table below)
Now I would like a sum of the last 3 months, so the november value should include all ReturnedItems between 25aug2012 and 26nov2012.
The value for october should show sum of ReturnedItems between 26jul2012.
Year | Month | Returned Items | Returned Items last 3 months |
---|---|---|---|
2012 | jul | 1.000 | 1.000 |
2012 | aug | 2.000 | 3.000 |
2012 | sep | 3.000 | 6.000 |
2012 | oct | 4.000 | 9.000 |
2012 | nov | 5.000 | 12.000 |
The column returned items last 3 months shows what i want in the table at the 30th of Nov 2012, and with data from the 1st of Jul 2012.
I have tried with a set analysis ;
sum( {$<Year = , Month = , Week = , Date = {"<=$(=date(max(DateValid))) >=$(=date(addmonths(max(DateValid),-3))) "}>} ReturnedItems)
with the Total <allmost all combination of keys>, All and aggr together with rangesum none of them give me the result i want. Most often they only gives a result for sep, oct, and nov (and not the sum of the last 3 months from that date).
Thanks in advance
Hi,
Try Chart Properties -> Expressions -> Accumulation -> Accumulate 3 steps back
This will work for you
Regards,
Jagan.
Rangesum is sadly not an option since all months needs to be "shown" in the table.
When the user selects year = 2012 the value for Jan-2012 must be the value for Nov-2011 -> Jan-2012 and not only Jan-2012.
Can't see why this requirement should be impossible...
same issues as with the use of rangesum(above()).