topic Pivot Table - How to create calculated measures at summary level? in App Development

Pivot Table - How to create calculated measures at summary level?

satya_s — Tue, 12 Dec 2023 14:37:02 GMT

Hi,

I am trying to replicate the following pivot table which is in Excel into Qlik Sense.

Excel

Qlik Sense - Pivot Table

I am not sure how to create the TNM and TNM% within the pivot table in order to replicate the same format as in excel. I need some direction on how to create these calculated measures which will display the data in the same format as excel:-

TNM = (Sum of TRAFFIC MARGIN-Sum of ACTUAL_RETAIL_CARR)

TNM % = (Sum of TRAFFIC MARGIN-Sum of ACTUAL_RETAIL_CARR)/Sum of AMOUNT

I am using SQL to extract the data from Oracle database. I have hard coded 'TNM' as Traffic Type and created a calculated measure but can't get the same format as excel

Any suggestions please?

If anyone has done something similar then please share your example.

Thanks !!

Re: Pivot Table - How to create calculated measures at summary level?

Or — Tue, 12 Dec 2023 14:42:19 GMT

You can format the data explicitly within the measure, e.g.

If(Dimension = 'SomeValue',Num(Sum(Field),'#,##0'),

if(Dimension = 'SomeOtherValue',Num(Sum(Field)/Sum(Field2),'#,##0.00%')))

Note that you will need to set the format to Auto so Qlik doesn't override these formats with something else.

Re: Pivot Table - How to create calculated measures at summary level?

satya_s — Tue, 12 Dec 2023 18:31:52 GMT

Hi,

Thanks for your reply.

Sorry, I am not clear.

I can create a new calculated measure but how would I get a summary row which shows 'TNM' with the calculated values? Do you have an pivot table example in similar format where the calculated dimension and measure is outside the pivot.

I tried to create a calculated measure within the SQL but the issue was that for the 'TNM' row, it is displaying all the other measures ( Traffic Margin, Amount etc.) which is not what I am looking for. I just need 1 row at the end showing the 'TNM' calculated values.

Thanks !!