Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with set analysis

Hi everyone!

I have this expression: sum(aggr(if(sum({<year={$(=(DATA_INI))}>} valor )<>0 and (sum( {<year={$(=(DATA_FIM))}>}  valor )<>0),sum({<year={$(=(DATA_FIM))}>} value),0),filial,categoria_produto,subcategoria_produto,grupo_produto,subgrupo_produto,DESC_PRODUTO))

What is the problem? So, when I did this expression with the current year, works perfectly.. but, the expression (DATA_FIM) is the last year, for example:

Current Year = 2016

Last year = 2015

I get the year with that expression( =GetFieldSelections(year)) and the last year(=GetFieldSelections(year) -1).

When I put the current year(2016), the results is correct. When I put last year(2015), the results is wrong.

That expression is in a pivot table like this:

FilialCategoriaDimension...nCurrent YearLast Year
xxxxgggggaaaaaaaa10090

Someone have a Idea?

1 Solution

Accepted Solutions
sunny_talwar

May be try this:

Sum({<year={$(=DATA_FIM)}>} Aggr(If(Sum({<year={$(=DATA_INI)}>} valor) <> 0 and Sum({<year={$(=DATA_FIM)}>}  valor) <> 0), Sum({<year={$(=DATA_FIM)}>}value), 0), filial, categoria_produto, subcategoria_produto, grupo_produto, subgrupo_produto, DESC_PRODUTO))

View solution in original post

4 Replies
sunny_talwar

May be try this:

Sum({<year={$(=DATA_FIM)}>} Aggr(If(Sum({<year={$(=DATA_INI)}>} valor) <> 0 and Sum({<year={$(=DATA_FIM)}>}  valor) <> 0), Sum({<year={$(=DATA_FIM)}>}value), 0), filial, categoria_produto, subcategoria_produto, grupo_produto, subgrupo_produto, DESC_PRODUTO))

Not applicable
Author

Sunny T, thank you so much! That's work perfectly.But I don't understand the logic behind this. Can you explain to me?

sunny_talwar

When you don't put set analysis in your outer Sum() function is adheres to the selections you have made. When 2015 is selected it would want to show 2015's result, but the imaginary table created by Aggr() function is only showing 2014. So unless we specify the outer sum to look for 2014 ({<year={$(=DATA_FIM)}>}) it will continue to look for current selected year and show null

Not applicable
Author

Thank you Sunny T for your explanation! Now I understand! Have a nice day!