Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Month | Lob | Amount | YTD |
1/1/2010 | A | 10 | 10 |
1/1/2010 | B | 15 | 15 |
1/1/2010 | C | 20 | 20 |
2/1/2010 | A | 25 | 35 |
2/1/2010 | B | 30 | 40 |
2/1/2010 | C | 35 | 55 |
3/1/2010 | A | 40 | 75 |
3/1/2010 | B | 45 | 85 |
3/1/2010 | C | 50 | 105 |
The YTD column is the expected result. Can you please help me with the expression using the sample data.
Hi
Use this script (modified to fit your data):
LOAD
TranDate,
Product,
Qty,
If (Product = Previous(Product) AND Year(TranDate) = Year(Previous(TranDate)), RangeSum(Peek('YTD'), Qty), Qty) AS YTD,
If (Product = Previous(Product) AND Month(TranDate) = Month(Previous(TranDate)), RangeSum(Peek('YTD'), Qty), Qty) AS MTD
RESIDENT tmpData
ORDER BY Product, TranDate
;
See attached for an example.
Hope that helps
Jonathan
Thanks Jonathan...
But I was trying to handle it in an expression...is that possible?
Perhaps this?
rangesum(Amount,if(year(Month)=year(above(Month)),above(YTD)))
I generally try to avoid using above(), though, as it depends on the exact way the chart is displayed and sorted. I prefer a data solution. I'd create an AsOf table. See the below post for a similar question and my suggested solution.
Can I use SET analysis to show YTD on Bar chart using the above data set?
I was able to put it in a pivot without any dimension, but it fails when we add a dimension.
=sum({<Month={"<=Month">}>} Amount) (Syntax might be incorrect)
Also I dnot want to have full accumulation option to be selected. Since when I select only 'feb' it should show me just one bar with FEB YTD value, which is not the case in full accumulation.
Thanks!!
You can't use set analysis for this because the set is only calculated once, not once for each row (each Month in this case) in your chart.