topic Re: Function AGGR in Set Analisys(pivot table) in QlikView

Function AGGR in Set Analisys(pivot table)

Fri, 29 Jan 2016 20:11:31 GMT

Hi, someone help me please!

I have probem with the function AGGR because, when I put only one dimension, the value is different that when I put others dimensions in the sum. For example:

When I put: sum(aggr(if(sum({<ANO={$(=YEAR(DATA_INI))}>} valor )<>0 and (sum( {<ANO={$(=YEAR(DATA_INI_ANT))}>} valor )<>0),sum({<ANO={$(=YEAR(DATA_INI))}>} valor ),0),filial))

That's work and I can to see the value of the filial, and the sum is correct.

But, when I try to put something like that: sum(aggr(if(sum({<ANO={$(=YEAR(DATA_INI))}>} valor )<>0 and (sum( {<ANO={$(=YEAR(DATA_INI_ANT))}>} valor )<>0),sum({<ANO={$(=YEAR(DATA_INI))}>} valor ),0),filial,otherdimension))

The value is different. I understood why that happens but I need to solve this problem. Probably the solution is make the same but without AGGR, I tried a lot, but nothing.

Ps: Sorry because my english is horrible.

Re: Function AGGR in Set Analisys(pivot table)

sunny_talwar — Fri, 29 Jan 2016 21:28:54 GMT

What is the logic behind adding otherdimension in your Aggr? I am not sure I understand why you want to add it when you getting the result without it?

Re: Function AGGR in Set Analisys(pivot table)

swuehl — Fri, 29 Jan 2016 22:26:36 GMT

You probably know that this kind of expression is used to express the sum-of-rows in a pivot table.

If your sum-of-rows is different from the expression total (e.g. because you may have records linking multiple times into different segments of your table), then adding dimensions will make a difference.

But maybe you can add dimensions, still achieving the same using:

sum(

aggr(

if(sum({<ANO={$(=YEAR(DATA_INI))}>} TOTAL<filial> valor )<>0 and (sum( {<ANO={$(=YEAR(DATA_INI_ANT))}>} TOTAL<filial> valor )<>0),sum({<ANO={$(=YEAR(DATA_INI))}>} TOTAL<filial> valor ),0)

,filial,otherdimension)

)

Re: Function AGGR in Set Analisys(pivot table)

Mon, 01 Feb 2016 16:39:53 GMT

Sunny, when you make a Pivot table, if you intend to see others levels(for example, you need to see a car, and all colors available) , you need to use Aggr function. The problem is when you use SubTotal, because when you have a lot of dimensios, the subtotal don't work. It's difficult to explain, maibe swuehl can to explain better than me.

Re: Function AGGR in Set Analisys(pivot table)

Mon, 01 Feb 2016 16:40:35 GMT

Swehl, thats work! Thank you for your attention!