topic Re: Aggregation issue when expression has rolling 12 months in QlikView

Aggregation issue when expression has rolling 12 months

rsdhavle — Fri, 16 Dec 2016 12:02:22 GMT

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.

Re: Aggregation issue when expression has rolling 12 months

PrashantSangle — Fri, 16 Dec 2016 12:05:36 GMT

Hi,

can you post your expression

might be you need nullify Month field in expression

REgards

Re: Aggregation issue when expression has rolling 12 months

snehamahaveer — Fri, 16 Dec 2016 12:07:13 GMT

Hi Rohan,

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

Re: Aggregation issue when expression has rolling 12 months

rsdhavle — Fri, 16 Dec 2016 12:11:22 GMT

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

Re: Aggregation issue when expression has rolling 12 months

rsdhavle — Fri, 16 Dec 2016 12:15:50 GMT

RangeSum on total expression or instead of SUM?

Re: Aggregation issue when expression has rolling 12 months

snehamahaveer — Fri, 16 Dec 2016 12:21:00 GMT

Can you add an image or sample qvw. ?

Re: Aggregation issue when expression has rolling 12 months

Gysbert_Wassenaar — Fri, 16 Dec 2016 12:31:46 GMT

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

Re: Aggregation issue when expression has rolling 12 months

rsdhavle — Mon, 19 Dec 2016 12:44:28 GMT

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

Re: Aggregation issue when expression has rolling 12 months

rsdhavle — Mon, 19 Dec 2016 12:45:38 GMT

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

Re: Aggregation issue when expression has rolling 12 months

Gysbert_Wassenaar — Mon, 19 Dec 2016 12:48:56 GMT

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.

Re: Aggregation issue when expression has rolling 12 months

rsdhavle — Mon, 19 Dec 2016 13:03:22 GMT

Might be i have modified incorrectly..just to confirm..instead of SUM function everywhere i should use RangeSum function right?

Re: Aggregation issue when expression has rolling 12 months

Gysbert_Wassenaar — Mon, 19 Dec 2016 13:13:30 GMT

Ah, no. Replacing it everywhere won't work. If you read the document I linked to above you'll see that the expressions don't replace every sum with a rangesum. The rangesum function is not an aggregation function, but calculates the sum of all the values used as parameters. So rangesum( 12, 8, 10 ) returns 30. Rangesum can be used in combination with the Above function where the Above function returns a list of values from previous rows and then rangesum will sum those values.

Re: Aggregation issue when expression has rolling 12 months

rsdhavle — Mon, 16 Jan 2017 09:05:31 GMT

Hi Gysbert..we have implemented 12 month logic using RangeSum() but it is not considering negative values it seems.

Basically, i am trying to implement something like the attached sheet, formula's of the same are mentioned in the sheet.

The final output is L column highlighted in green.

Re: Aggregation issue when expression has rolling 12 months

Gysbert_Wassenaar — Wed, 18 Jan 2017 17:38:13 GMT

Rangesum does not disregard negative values unless you force it to do so. Can you post a small qlikview document that demonstrates the problem?

BTW this looks like a straightforward problem that can be solved with Rangexxx functions or with an AsOf table. Both options are explained in the document I linked to above.