topic Re: Qlik Pivot Table Custom Display of Values in Qlik Answers

Qlik Pivot Table Custom Display of Values

MMK — Tue, 11 Nov 2025 17:41:16 GMT

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.

Re: Qlik Pivot Table Custom Display of Values

robert_mika — Wed, 12 Nov 2025 04:23:28 GMT

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?

Re: Qlik Pivot Table Custom Display of Values

Amit_Prajapati — Wed, 12 Nov 2025 04:45:23 GMT

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.

Re: Qlik Pivot Table Custom Display of Values

MMK — Wed, 12 Nov 2025 06:43:48 GMT

Thanks @robert_mika ,@Amit, For your reply.

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

Re: Qlik Pivot Table Custom Display of Values

Amit_Prajapati — Wed, 12 Nov 2025 06:57:50 GMT

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);

Re: Qlik Pivot Table Custom Display of Values

MMK — Wed, 12 Nov 2025 08:58:48 GMT

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.

Re: Qlik Pivot Table Custom Display of Values

Amit_Prajapati — Wed, 12 Nov 2025 12:44:10 GMT

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