Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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))
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))
Sunny T, thank you so much! That's work perfectly.But I don't understand the logic behind this. Can you explain to me?
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
Thank you Sunny T for your explanation! Now I understand! Have a nice day!