Sum of Sales in 'Next' month - without After

[jonathjl]

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

[Clever_Anjos]

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))

[swuehl]

You can look into the concept of as-of-tables .

[jonathjl]

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;

[Clever_Anjos]

PFA

[Clever_Anjos]

Are you using MonthIsland as your dimension?

[Jason_Michaelides]

I'm a big fan of the Flag matrix at the moment. See attached.

Hope this helps,

Jason