Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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.

Please, someone can help me?

1 Solution

Accepted Solutions
MVP
MVP

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

4 Replies
MVP
MVP

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)

Not applicable

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.

MVP
MVP

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

Not applicable

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!

Community Browser