topic Aggregate function in QlikView

Aggregate function

Thu, 14 May 2015 13:17:37 GMT

Hi all!

I have a pivot table with a conditional expresion, one of the calculations is an Aggr function, the expression works, but when i'm going to do drilldown for a different dimension, the value of the dimensions are the same, only shows the total value.

How can i create this expression?, i need to do drilldown.

This is the expression:

=if(MIS_KPI_ID='LF',

(

Sum(TOTAL <MONTH> Aggr(Sum(TOTAL <MONTH,MIS_KPI_ID,CIA_KPI> {<MIS_KPI_ID = {'RPK'}>} VALUE),MIS_KPI_ID,MONTH,CIA_KPI))

Sum(TOTAL <MONTH> Aggr(Sum(TOTAL <MONTH,MIS_KPI_ID,CIA_KPI> {<MIS_KPI_ID = {'ASK'}>} VALUE),MIS_KPI_ID,MONTH,CIA_KPI))

),Sum(VALUE)

)

...and this the result of the pivot table:

Thanks for the help.

Best Regards.

Re: Aggregate function

sunny_talwar — Thu, 14 May 2015 13:26:48 GMT

I think you need to play around with the second total within Aggr(Sum( Total....)) to get it to give you the proper result.

Try removing and adding them one by one and see if that makes any difference:

=if(MIS_KPI_ID='LF',

(

Sum(TOTAL <MONTH> Aggr(Sum(TOTAL <MONTH,MIS_KPI_ID,CIA_KPI> {<MIS_KPI_ID = {'RPK'}>} VALUE),MIS_KPI_ID,MONTH,CIA_KPI))

Sum(TOTAL <MONTH> Aggr(Sum(TOTAL <MONTH,MIS_KPI_ID,CIA_KPI> {<MIS_KPI_ID = {'ASK'}>} VALUE),MIS_KPI_ID,MONTH,CIA_KPI))

),Sum(VALUE)

)

We might be able to give you a better solution if you can share a sample app.

Best,

Sunny

Re: Aggregate function

Thu, 14 May 2015 15:00:11 GMT

sunindia, thanks for your reply.

I cannot make it work, see attachment.

Bests Regards.

Re: Aggregate function

sunny_talwar — Thu, 14 May 2015 15:16:00 GMT

Use this expression (PFA)

=if(MIS_KPI_ID='LF',

(

Sum(TOTAL <MONTH, CIA_KPI> Aggr(Sum({<MIS_KPI_ID = {'RPK'}>} VALUE),MONTH, CIA_KPI, MIS_KPI_ID)) /

Sum(TOTAL <MONTH, CIA_KPI> Aggr(Sum({<MIS_KPI_ID = {'ASK'}>} VALUE),MONTH, CIA_KPI, MIS_KPI_ID))

)

Sum(VALUE)

)

Re: Aggregate function

Mon, 18 May 2015 15:45:27 GMT

sunindia‌

Thanks for your workaround, the solution works but has a strange behavior when you're going to do drilldown on the kpi calculated. If you're going to do drilldown in LF... disappears.

If you click on "show all"... reappears.

What could be happening?

Bests Regards.

Re: Aggregate function

sunny_talwar — Mon, 18 May 2015 15:55:30 GMT

It is strange. I have never seen this kind of behavior like this before and not sure what is causing this. I might have to play around with it to see the potential cause of this. Maybe you can create another pivot chart and see if the behavior is repeated or not.

Best,

Sunny

Re: Aggregate function

Tue, 09 Jun 2015 15:19:06 GMT

Thanks sunindia‌, the cause is because it's marked the option to delete nulls.