Hi,
I am trying to find Max, Min and Average value within last 12 Months.
Let say in below example
Sub Product , Product, BU, Abc, Xyz are my different dimensions at different granularity
if I have all of the above dimensions as filters, I should be able to get MAX, MIN, AVG $$ value in the Last 12 Months.
Month | Sub Product | Product | BU | $$ | Xyz | Abc |
1 | A1 | A | AB | 100 | ||
2 | A2 | A | AB | 101 | ||
3 | A3 | A | AB | 102 | ||
4 | A4 | A | AB | 103 | ||
5 | A5 | A | AB | 104 | ||
6 | A6 | A | AB | 105 | ||
7 | B1 | B | AB | 106 | ||
8 | B2 | B | AB | 107 | ||
9 | B3 | B | AB | 108 | ||
10 | B4 | B | AB | 109 | ||
11 | B5 | B | AB | 110 | ||
12 | B6 | B | AB | 111 |
Regards,
Nagaraj K
Hi ,
You Can try using this logic:
IF LOOKUP(SUM([$$]),-12)>0 THEN
WINDOW_AVG(SUM([$$]), -12, -1)
END