Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Keep values years comparison

Hello there!

I have values for years 2015 and 2014 for Transportation "RGSOZFV"

Page1.png

When I do not select any year is clearly to see the comparison for both years (2015-2014)

Nevertheless when I select a Year, the values for 2014 desappear.

Page2.png

Is it possible to keep the values for the last year even when I do not have values for the current year similar as when there is no year selected?

Greetings

4 Replies
Highlighted
MVP
MVP

Re: Keep values years comparison

When using advanced aggregation, you may need to apply set analysis to the outer as well as the inner aggregation function. For example, for your Servicios 2014 expression in Transportation table:

only(

{<FamiliaDescr=,MatDescripFINAL=,TipoDato={'NS_TipoServicio'} ,Año={'$(vMaxAñoAnt)'}>} TOTAL <Embarcacion,CargoADescr>

aggr(sum ( {<FamiliaDescr=,MatDescripFINAL=,TipoDato={'NS_TipoServicio'} ,Año={'$(vMaxAñoAnt)'}>} CanAliHue) ,Embarcacion,CargoADescr)

)

Highlighted
Not applicable

Re: Keep values years comparison

Thank you for your time swuehl, how does Inner aggregation function works exactly?

Highlighted
MVP
MVP

Re: Keep values years comparison

In this example, ONLY() is the outer aggregation function, and SUM() is the inner aggregation function of the advanced aggregation.

Look at advanced aggregation like you build a table chart with dimensions Embarcacion and CargoADescr

and a single expression Sum({...} CanAliHue).

The expression results form a vector and can be aggregated again by another aggregation function, the outer aggregation. Before the outer aggregation takes place, the temporary table will be limited to dimension values that match with the chart dimension values of the chart where your advanced aggregation is located in.

Highlighted
Not applicable

Re: Keep values years comparison

Thank you again swuehl‌, I apply the set analysis sintax that you gave me into the formula expression, and It works! Partially.

Page3.png

I need the totals on top, and when I use the aggr function for totals the value's detail desappear, here is the example, even the subtotals row.

Page4.png !

I tried using Sum(Aggr( as I usually do, but it doesn't work

=if(Dimensionality()>=1

,

//sum( Aggr(

sum({<TipoDato={'Movimiento'} ,Año={'$(vMaxAñoAnt)'}>}CostoMov)/ Only({<FamiliaDescr=,MatDescripFINAL=,TipoDato={'NS_TipoServicio'} ,Año={'$(vMaxAñoAnt)'}>} TOTAL <Embarcacion,CargoADescr>aggr(sum ( {<FamiliaDescr=,MatDescripFINAL=,TipoDato={'NS_TipoServicio'} ,Año={'$(vMaxAñoAnt)'}>} CanAliHue) ,Embarcacion,CargoADescr))

//,Embarcacion,CargoADescr))

,sum(Aggr(sum({<TipoDato={'Movimiento'} ,Año={'$(vMaxAñoAnt)'}>}CostoMov)/sum (TOTAL <CargoADescr> aggr(sum ( {<FamiliaDescr=,MatDescripFINAL=,TipoDato={'NS_TipoServicio'} ,Año={'$(vMaxAñoAnt)'}>} CanAliHue) ,CargoADescr)),CargoADescr))

)

Any Ideas?

Thank you