Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to display in a pivottable *and* a graph YTD information, which resets to 0 after each Yearstart (January).
I thought this would be easy to do, but alas I've not been able to do it.
Can anyone help me or suggest alternatives?
Kind Regards,
Dion
(Numbers are scrambled)
If you say, that you want to reset to zero, I assume you want to aggregate the numbers over the year (YTD).
This is what I can't see right now, it seems you just sum the numbers per month.
If you want to aggregate the numbers (YTD), you could use a rangesum() function, but you also need to define your columns segments appropriately. So you could try:
=aggr(rangesum(above(Sum({<MetricNumber={'2.4.2.2.'}>} Numbers),0,RowNo())),Year,NumMonth)
as expression in your table / line chart (charts in the container).
If for any reason your current results are ok, but you want zero on every January, try
=if(NumMonth=1, 0, Sum({<MetricNumber={'2.4.2.2.'}>} Numbers) )
Hope this helps,
Stefan