Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Aggregate function

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:

KPI_data2.jpg

Thanks for the help.

Best Regards.

1 Solution

Accepted Solutions

Re: Aggregate function

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)

)

View solution in original post

6 Replies

Re: Aggregate function

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

Not applicable

Re: Aggregate function

sunindia, thanks for your reply.

I cannot make it work, see attachment.

Bests Regards.

Re: Aggregate function

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)

)

View solution in original post

Not applicable

Re: Aggregate function

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

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

Not applicable

Re: Aggregate function

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