topic Get Total Sum from conditional SUM in QlikView

Get Total Sum from conditional SUM

Mon, 15 Feb 2016 16:55:03 GMT

Hello there:

I have an Straight Table and I need to replicate sum Totals for each month selected, something like when May is selected:

In my Qlikview file shows the information like:

When I hide the Column AñoNombreMes, the result is different from the sum of values

Here is my expression:

=if

(Only ({<MesT=,Mes>}Codigo_IndicadorT)='PROFIT' ,

Sum({<EscenarioT={'REAL'},AñoT={'$(vMaxAño)'},Mes_Num={'>=1 <=$(vMaxMesNum)'},Mes=,MesT=,Total_MedidaT={'=not isnull(Total_MedidaT)'}>} Datos)

Count({<Año={'$(vMaxAño)'},Mes=,MesT>} DISTINCT(FechaCont))

)

I Wonder if it is possible to get the Total sum from the conditional SUM or change the expression in order to get the correct sum of values.

Thanks in advance.

My best regards.

Re: Get Total Sum from conditional SUM

swuehl — Mon, 15 Feb 2016 17:25:44 GMT

Maybe using advanced aggregation, something like

=Sum({<EscenarioT={'REAL'},AñoT={'$(vMaxAño)'},Mes_Num={'>=1 <=$(vMaxMesNum)'},Mes=,MesT=,Total_MedidaT={'=not isnull(Total_MedidaT)'}>}
Aggr(
if
(Only ({<MesT=,Mes>}Codigo_IndicadorT)='PROFIT' ,
//Aggr(
Sum({<EscenarioT={'REAL'},AñoT={'$(vMaxAño)'},Mes_Num={'>=1 <=$(vMaxMesNum)'},Mes=,MesT=,Total_MedidaT={'=not isnull(Total_MedidaT)'}>} Datos)
*
Count({<Año={'$(vMaxAño)'},Mes=,MesT>} DISTINCT(FechaCont))
//,EscenarioT, AñoNombreMes ,AreaT ,Descripcion_IndicadorT)
)
,[AñoNombreMes]))

Re: Get Total Sum from conditional SUM

sathishkumar_go — Mon, 15 Feb 2016 17:52:02 GMT

HI,

Try This

=sum({<MesT=,Mes, Codigo_IndicadorT={'PROFIT'}, EscenarioT={'REAL'},AñoT={'$(vMaxAño)'},Mes_Num={'>=1 <=$(vMaxMesNum)'},Mes=,MesT=,Total_MedidaT={'=not isnull(Total_MedidaT)'}>}
aggr(
Sum({<MesT=,Mes, Codigo_IndicadorT={'PROFIT'}, EscenarioT={'REAL'},AñoT={'$(vMaxAño)'},Mes_Num={'>=1 <=$(vMaxMesNum)'},Mes=,MesT=,Total_MedidaT={'=not isnull(Total_MedidaT)'}>} Datos)
* Count({<MesT=,Mes, Codigo_IndicadorT={'PROFIT'}, Año={'$(vMaxAño)'},Mes=,MesT>} DISTINCT(FechaCont))
,[AñoNombreMes])
)

-Sathish

Re: Get Total Sum from conditional SUM

Mon, 15 Feb 2016 18:10:07 GMT

Thank you Sathish G, it works perfect.

My best regards.

Re: Get Total Sum from conditional SUM

Mon, 15 Feb 2016 18:11:48 GMT

Thank you for you time swuehl, it works, nevertheless when I unmark PROFIT the column doesn't show values.