Pivot Table custom Display of Percentages

[MMK]

Hi All,

Thanks in advance for your support.

I am having data for two Quarters. I want to show that Percentage values in a pivot table. 

the calculation logic Summary = ANSGTER is the ratio of ANSTC and ANSIG (ANSGTER = ANSTC/ANSIG). In sample data just i gave values for ANSGTER but in Qlik Front end i need to show those numbers using above formula.

I want to show the pivot table in Qlik like above screen shot.

In Pivot table,

Rows - Summary, AN, State (Row Hierarchy Level1 --> Summary, Level2 --> AN and Level3 --> State)

Column - Period

[Chanty4u]

Try this 

Num(

    Sum( AGGR(Sum(ANSTC), Summary, AN, State, Quarter) )

    /

    Sum( AGGR(Sum(ANSIG), Summary, AN, State, Quarter) ),

    '0.00%'

)

[Daniel_Castella]

Hi @MMK 

I'm not sure which is exactly the input data that you are using, but I understand from your description that you only have ANSTC and ANSIG values and you need to build ANSGTER based on both. I used the code below to load the data from the file provided, but only retrieving the raw data for ANSTC and ANSIG (excluding also the total values). 

A:
LOAD
Country,
State,
Period,
AN,
Summary,
VALUE
FROM [lib://DataFiles/Sample Data v2.xlsx]
(ooxml, embedded labels, table is Data)
WHERE Len(Country)>0 and Len(State)>0 and (Summary='ANSTC' or Summary='ANSIG');

Concatenate(A)
LOAD
Country,
State,
Period,
'987025' as AN,
Summary as Summary2,
'ANGSTER' as Summary,
VALUE
RESIDENT A
WHERE Summary='ANSTC';

Concatenate(A)
LOAD
Country,
State,
Period,
'987025' as AN,
Summary as Summary2,
'ANGSTER' as Summary,
VALUE
RESIDENT A
WHERE Summary='ANSIG';

With the concatenates, I'm building the ANGSTER data and I can recover it in the front end using the following formula in the pivot table:

If(Summary='ANSIG' or Summary='ANSTC', Sum(VALUE), Sum({<Summary2={'ANSTC'}>}VALUE) / Sum({<Summary2={'ANSIG'}>}VALUE))

The result is this output:

Let me know if it works for you.

Kind Regards

Daniel

[MMK]

Hi @Daniel_Castella , Thanks for your reply.

Above solution worked well. But is there any way to do with Dimensionality concept in front end, without touching backend script. I gave simple sample data, My practical is very big which is increasing the volume of data.

[MMK]

Thanks @Chanty4u , Your logic is not working for me.

[Daniel_Castella]

Hi @MMK 

I don't think this can be done only in the front end, or at least I don't know how to do it. The issue here is that you need to build the ANSGTER inside the summary dimension to be displayed as another row in the pivot table. To do this you need to add these rows in the backend.

Other solution is to have two separate pivot tables. Like in the image below. You can have one pivot table for the numerator and denominator and later other for the percentage. However, I don't know if it is possible to merge them in a single table due to the dimension incompatibility.

In any case, Qlik best practices recommend to perform all this kind of transformations in the backend. For this reason, my solution was made there.

Kind Regards

Daniel