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:
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.
