topic Re: Problem with set analysis in QlikView

Problem with set analysis

Wed, 25 Nov 2020 16:16:04 GMT

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:

Filial	Categoria	Dimension...n	Current Year	Last Year
xxxx	ggggg	aaaaaaaa	100	90

Someone have a Idea?

Re: Problem with set analysis

sunny_talwar — Thu, 10 Mar 2016 00:43:42 GMT

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

Re: Problem with set analysis

Thu, 10 Mar 2016 18:30:34 GMT

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

Re: Problem with set analysis

sunny_talwar — Thu, 10 Mar 2016 19:42:34 GMT

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

Re: Problem with set analysis

Fri, 11 Mar 2016 12:38:21 GMT

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