Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a simple QV in-memory table:
LOAD * INLINE [
Item, SalesMonth, Sales
A, 1, 10
A, 2, 20
A, 3, 30
A, 4, 40
A, 5, 50
A, 6, 60
A, 7, 70
A, 8, 80
A, 9, 90
A, 10, 100
A, 11, 110
A, 12, 120
];
And want a chart that displays Sum(Sales) / Sum(Sales for the month 6 months from now) by Month.
So for Month 1 = 10/70 (10 for month 1, 70 for month 1+6 = 7)
Month 2 = 20/80
Month 3 = 30/90
etc ...up to month 6
Easy to do with 'After' - but is there another way - with Aggr or Set analysis?
Thanks
Using After(or below) is a much better approach, but if you insist
1) Create a disconected(Data Island) SalesMonth, for example SalesCalendar
2) Your expression would be
sum(if(SalesCalendar=SalesMonth,Sales))/sum(if(SalesCalendar=SalesMonth+6,Sales))
You can look into the concept of as-of-tables .
Doesn't work ...When I load the below and have a chart, but Item and SalesMonth
Exp1: Sum(If(MonthIsland = SalesMonth,Sales,0)) shows 10 for Month 1, 20 for Month 2, etc (as expected)
Exp2: Sum(If(MonthIsland = SalesMonth + 6,Sales,0)) - shows exactly the same thing
T_Sales:
LOAD * INLINE [
Item, SalesMonth, Sales
A, 1,10
A, 2,20
A, 3,30
A, 4,40
A, 5,50
A, 6,60
A, 7,70
A, 8,80
A, 9,90
A, 10,100
A, 11,110
A, 12,120
A,13,130
A,14,140
];
T_Island:
Load distinct SalesMonth as MonthIsland resident T_Sales;
PFA
Are you using MonthIsland as your dimension?
I'm a big fan of the Flag matrix at the moment. See attached.
Hope this helps,
Jason