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

[satya_s]

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 !!

[Or]

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.

[satya_s]

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 !!