Aggregation issue when expression has rolling 12 months

[rsdhavle]

Hi,

I have one expression with rolling 12 months when i write this expression in text object calculation is perfect. When i write this expression against Month Dimension in a straight table it gives me incorrect numbers as it seems aggregation is happening. How can we achieve so that it will give me correct expression value against each month.

[PrashantSangle]

Hi,

can you post your expression

might be you need nullify Month field in expression

REgards

[snehamahaveer]

Hi Rohan,

Why don't you try using RangeSum() function instead?

[rsdhavle]

Sum({$<Year=,MONTH_SHORT_NAME=,YearMonth={'>$(=Max(YearMonth)-100) <=$(=Max(YearMonth))'},Prod1 -={'ABC'}>} Expense) /

SUM({<Year=,MONTH_SHORT_NAME=,YearMonth={'>$(=Max(YearMonth)-100) <=$(=Max(YearMonth))'}>}Rev)/1000

This is sample expression i am using against Month Dimension

[rsdhavle]

RangeSum on total expression or instead of SUM?

[snehamahaveer]

Can you add an image or sample qvw. ?

[Gysbert_Wassenaar]

See here: Calculating rolling n-period totals, averages or other aggregations

[rsdhavle]

Hi Sneha,

This is sample formula.

Sum({$<Year=,MONTH_SHORT_NAME=,YearMonth={'>$(=Max(YearMonth)-100) <=$(=Max(YearMonth))'},Prod1 -={'ABC'}>} Expense) /

SUM({<Year=,MONTH_SHORT_NAME=,YearMonth={'>$(=Max(YearMonth)-100) <=$(=Max(YearMonth))'}>}Rev)/1000

Even if i use Rangesum instead of Sum it gives incorrect values

[rsdhavle]

I tried using Rangesum options however i get different values when i use that function

[Gysbert_Wassenaar]

That's amazing. An expression normally returns only one value. And if you don't change the data or the selections it's always the same value too.