4 Replies Latest reply: Jan 22, 2016 9:53 AM by William Lima

# Problems with set analisys

Hi everybody,

I need a little help.(Sorry, my English is horrible).

I'm trying to sum with that conditions : if(sum( {<ANO={\$(=YEAR(DATA_INI))}>} valor )<>0 and (sum( {<ANO={\$(=YEAR(DATA_INI_ANT))}>} valor )<>0),0,1)

That expression means that, with I have a sale in the last year, in the same time(month, day depend of the user selection), of the current year, I need to sum that value.

This expression work, when I put "0" or "1", but don't work when I try to sum.

• ###### Re: Problems with set analisys

You can't just embed a Sum() into another Sum() without advanced aggregation (aggr() function).

In what context are you using this expression? Client?

Then maybe like this:

Sum( {<Client = {"=sum( {<ANO={\$(=YEAR(DATA_INI))}>} valor )<>0 and (sum( {<ANO={\$(=YEAR(DATA_INI_ANT))}>} valor )<>0"}, ANO={\$(=YEAR(DATA_INI))} >} valor)

• ###### Re: Problems with set analisys

Hi Swuehl, thanks for your answer and your attention, but actually the problem isn't it the sum, because when I put:

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

the pivottable shows exactly that I want. The problem is the subtotal of the pivot table that shows the sum of all the sum, not only with the conditions. The total of the sum with conditions need to be less than the total. I try to make a sentence where the logic is, when the outlet have in the last and the current year,month and day sales, I sum the value. Because , I can tho close a outlet in the last year and because that I can't compare.

• ###### Re: Problems with set analisys

You need to use adavanced aggregation to evaluate your expression in the context of your outlets even when calculating a partial sum:

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)

, YourOutletField

))

Instead of YourOutletField, use the dimension fields you are using in your pivot table chart (outlet, as far as I understand).

• ###### Re: Problems with set analisys

Exactly! The problem was solved with that condition:

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

Thats work!

Thank you!