topic Re: Aggr () in pivot table in QlikView

Aggr () in pivot table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a pivot table. The points expression is not giving the sum of rows. I understand that I need to use the aggr() to get the correct sum of rows but I am not able to correctly place that function. Can anyone please help? Thanks!

Dimension is a calculated dimension : If((isnull(WinLossDate) or num#(WinLossFiscalYear&num(WinLossFiscalQuarter,00))<'201201'),null(),Owner)

Points expression :

if ( (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 100000, 0.000,

if( (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) >= 100000 and (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 250000 , 0.125,

if( (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) >= 250000 and (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 500000 , 0.310 ,

if ( (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) >= 500000 and (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 1000000, 0.625,

if ( (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) >= 1000000 and (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 2500000 , 1.25 ,3.100 )))))

Re: Aggr () in pivot table

GabrielAraya — Thu, 08 Sep 2016 18:50:36 GMT

Hi ...

Did you test ..

Sum ( Aggr ( if ( (sum({<FunnelStamp2={'Current'},Stage={' ......... ), [Project Owner], Dimension2, dimension3, ...
))

Regards

Re: Aggr () in pivot table

Anonymous — Thu, 08 Sep 2016 18:57:51 GMT

Try this:

sum(aggr(if(sum(Expression1)<60000,1,10),Dim2))

where Dim2 = hidden dimension ( + )

Hope it helps.

Fernando V.

Re: Aggr () in pivot table

ToniKautto — Fri, 09 Sep 2016 08:39:16 GMT

The expression looks over complicated. Wouldn't the belwo expression give the same result?

if ( (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 100000, 0.000,

if( (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 250000 , 0.125,

if( (sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 500000 , 0.310 ,

if ((sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 1000000, 0.625,

if ((sum({<FunnelStamp2={'Current'},Stage={'5.0-Closed Design Win','5.0-Closed Project Win','5.0-Closed Booked'}>}Amount)) < 2500000 , 1.25 ,

3.100 )))))

I would recommend that you try and add your calculated dimension to the data model during application reload, so that you get a real dimension in your chart. Then you can create a column total by using Aggr(), where you list all dimensions in your chart.

Sum(Aggr(<the expression above>, Dim1, Dim2, ... DimN))