topic Keep values years comparison in QlikView

Keep values years comparison

Tue, 25 Aug 2015 17:31:01 GMT

Hello there!

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

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.

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

Re: Keep values years comparison

swuehl — Tue, 25 Aug 2015 19:02:01 GMT

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)

)

Re: Keep values years comparison

Tue, 25 Aug 2015 22:11:24 GMT

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

Re: Keep values years comparison

swuehl — Tue, 25 Aug 2015 22:20:52 GMT

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.

Re: Keep values years comparison

Tue, 25 Aug 2015 23:49:55 GMT

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

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.

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