Problems with set analisys

[#1]

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.

Please, someone can help me?

[swuehl]

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)

[#3]

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.

[swuehl · Accepted solution]

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

[#5]

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!