Hello Everyone,
I found myself a challenge in comparing data to previous periods.
Basically I am having a look at the total Value of stock position (Sum([Value])) at each period in time.
I have these Fields:
[Period]
[Quantity]
[Product]
[Value]
So I am looking at an area chart, with [Period] as 1st Dimension -Stack the and sum([Value]) as height .
The challenge is that I would like to split the sum of value in two categories, for each period in time.
1st Category: Items whose quantity has changed between the 3 years preceding the Period in question.
2nd Category: all the rest
I am checking if it the quantity has changed in a period by simple checking if the Min(Quantity) and the Max(Quantity) within that period is the same or different.
However, I found myself incapable of setting the right formula to define the sets of values as the Period being plotted and the three years preceding that Period.
I was playing with Aggr() the most, with the following construction:
=if(Aggr(Max(if([Period]>AddYears([Period],-3),[Quantity]))-Min(if([Period]>AddYears([Period],-3),[Quantity])),[Product])=0,'1st Category','2nd Category'))
but this doesn't really work, it just splits the between products that have never changed quantities across the full extension of the Period data, and those who have.
I tried working with set analysis and rangesum(above() constructions, but I can't say I really understand them.
Hope you can help, will appreciate it!
