Qlik Pivot Table Custom Display of Values

[MMK]

I am having data at different levels,

Globe, Country, State Levels. I want to show values in a Pivot table with County, State as Rows Summary as Column and Value is the metric to show.

Sample file is given, i want to show the values in pivot table as expected. 

Under summary column we have 3 values - ANSTC, ANSIG are numbers and ANSGTER is the percentage (ratio of ANSTC/ANSIG)

How to achieve this, Country, State and Summary are not fixed. User May change positions of Dimensions from Row's to Columns and Columns to Row's.

[robert_mika]

If the dims are not fixed it will be harder to predict how to get the values..

It is possible to educate users to keep consistency?

[Amit_Prajapati]

Hi @MMK , To ensure smooth data flow, it's important that users maintain data in a consistent format.

As @robert_mika  rightly pointed out, we should inform the business about this issue and guide them on the importance of using a single, standardized format across all data inputs.

[MMK]

Thanks @robert_mika ,@Amit, For your reply. 

If Dimensions are fixed, Help me how to show that as shown in image.

[Amit_Prajapati]

Hi @MMK ,

Start by loading the first sheet where the data is maintained. Once the data is loaded, create a basic pivot table using the following setup:

• Dimensions: Country and State

• Columns: Summary

• Presentation: Ensure the pivot table is fully expanded to display all data clearly.

Script : 

LOAD
Country,
State,
AN,
Summary,
CALCULATOR,
VALUE,
"Comment",
H,
"I",
J,
K,
L,
M
FROM [Sample Data V1.xlsx]
(ooxml, embedded labels, table is Sheet2);

[MMK]

Hi @Amit_Prajapati ,

Sum(VALUE) - simple expression works for Numbers...But it is failing for Percent Column (First column in your image). Summary = ANSGTER value getting wrong.

[Amit_Prajapati]

Hi @MMK , Try with below expression to get the required output.

if(Dimensionality()=1 and Summary='ANSGTER',

(sum(total<Country>{<Summary={'ANSTC'}>}VALUE)/sum(total<Country>{<Summary={'ANSIG'}>}VALUE)),

Sum(VALUE)
)