topic Re: Sum of a variable in set analysis in QlikView

Sum of a variable in set analysis

Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I'm trying to build a formula wich calculates the tax liability. The formula works fine only the part which is bold ($(vTax)) gives me trouble. I'm getting following error: nested aggregation not allowed.

Code:

//Tax liability

if(ChType2= 'B' and left([Dim3Name$(vLang)2], 6) = '450001',

if($(vTax) > 0,

Num(Sum({<ChType2={"B"}, BookingYear={"$(=Max(BookingYear))"}>} $(vTax), '$(vCurrency) #.##0,00;$(vCurrency) -#.##0,00'),

Num(Sum({<ChType2={"B"}, BookingYear={"$(=Max(BookingYear))"}>} TXAmount * Factor2), '$(vCurrency) #.##0,00;$(vCurrency) -#.##0,00')),

//Others

Num(Sum({<ChType2={"B"}, BookingYear={"$(=Max(BookingYear))"}>} TXAmount * Factor2), '$(vCurrency) #.##0,00;$(vCurrency) -#.##0,00'))

So i need a solution to get the sum from this variable in my calculation, does anyone have an idea how i can get the sum of a variable in set analysis???

Re: Sum of a variable in set analysis

trdandamudi — Fri, 02 Dec 2016 11:49:54 GMT

May be as below: (Just added = )

//Tax liability

if(ChType2= 'B' and left([Dim3Name$(vLang)2], 6) = '450001',

if($(vTax) > 0,

Num(Sum({<ChType2={"B"}, BookingYear={"$(=Max(BookingYear))"}>} $(=vTax), '$(vCurrency) #.##0,00;$(vCurrency) -#.##0,00'),

Num(Sum({<ChType2={"B"}, BookingYear={"$(=Max(BookingYear))"}>} TXAmount * Factor2), '$(vCurrency) #.##0,00;$(vCurrency) -#.##0,00')),

//Others

Num(Sum({<ChType2={"B"}, BookingYear={"$(=Max(BookingYear))"}>} TXAmount * Factor2), '$(vCurrency) #.##0,00;$(vCurrency) -#.##0,00'))

Re: Sum of a variable in set analysis

marcus_sommer — Fri, 02 Dec 2016 11:50:31 GMT

Your variable contained an aggregation like sum or count and if you replaced the variable with it's content you will get an expression like: sum(sum(... if all the if-conditions and set analysis would be removed. This kind of aggregation isn't allowed respectively needs to extended or wrapped with an aggr-function: AGGR...

- Marcus

Re: Sum of a variable in set analysis

Fri, 02 Dec 2016 12:05:31 GMT

Thanks for your answer.

But my variable doesn't contain an aggregation (like for example sum).

vTax:

Num(

IF(($(eTaxCalculation))<($(vTaxlimit1)),0,

IF(($(vReducedTarif))=0,

IF(($(eTaxCalculation))>($(vTaxlimit2)),

IF(($(eTaxCalculation))>($(vTaxlimit3)),

IF(($(eTaxCalculation))>($(vTaxlimit4)),((($(eTaxCalculation))-($(vTaxlimit4)))*($(vTaxlevel1)))+($(vTaxamount1)),((($(eTaxCalculation))-($(vTaxlimit3))))*($(vTaxlevel2))+($(vTaxamount2))),

((($(eTaxCalculation))-$(vTaxlimit2))*($(vTaxlevel3))+($(vTaxamount3)))),

($(eTaxCalculation))*($(vTaxlevel4))),($(eTaxCalculation))*($(vTaxlevel1))))

, '#.##0,00')

Second remark is when i wan't to use aggr() i need to use the dimensions in the formula, but i have tried it and i'm still getting an error.

My dimensions: 1. =If(ChType2 = 'B', Dual(Text([Dim1Name$(vLang)2]),Num(Order2)))

2. =If(ChType2 = 'B', [Dim2Name$(vLang)2])

3. =If(ChType2 = 'B', [Dim3Name$(vLang)2])

If i try it in a text object with following expression then i get the correct amount:

=Num(Sum({<ChType2={"B"}, BookingYear={"$(=Max(BookingYear))"}>} aggr($(vTax), BookingYear)), '$(vCurrency) #.##0,00;$(vCurrency) -#.##0,00')

Re: Sum of a variable in set analysis

marcus_sommer — Fri, 02 Dec 2016 15:54:52 GMT

For this you would need to expand all variables and quite probably contained eTaxCalculation or another variable there such an aggregation. To get an result with an aggr-function you need to specify real dimensions to it - it won't worked with calculated dimensions - in your case I would try:

... aggr($(vTax), [Dim1Name$(vLang)2], [Dim2Name$(vLang)2], [Dim3Name$(vLang)2]) ...

I fear if this don't work you will need a different approach with bigger changes to your variables, expressions and maybe the datamodel ... maybe starting with some simplifying to your expressions-approach.

- Marcus

Re: Sum of a variable in set analysis

Mon, 05 Dec 2016 07:26:52 GMT

Ok, i will try this, thank you

Re: Sum of a variable in set analysis

Thu, 19 Jan 2017 07:48:31 GMT

Hello,

Thanks again for your answer. It took me this long to test this issue again. Your answer helped me to look in the right direction.
I used the AGGR function and after simplyfiyng my dimensions I got it working.

Thanks for your help!