Sum of a variable in set analysis

[#1]

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

[trdandamudi]

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

[marcus_sommer]

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

[#4]

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

[marcus_sommer · Accepted solution]

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

[#6]

Ok, i will try this, thank you

[#7]

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!