Pivot Table

[Upali_Wijearatne]

Hi

I have created a pivot table in qvw file with available data and it is as follows

The records in black are already in my pivot table and I want add the records in Red to my table. But I am unable to do it . Kindly help me achieve it please.

[steeefan]

Total values can be added by just activating "Show totals" in the dimension "Product". For Growth, I suggest that you calculate those values in the load script. Otherwise it will probably be tricky to add those right there in the pivot table.

[Upali_Wijearatne]

Thanks 

Can you pls elaborate  how to activate show total value please

[steeefan]

Certainly:

1. Switch to Edit mode
2. Select your pivot table
3. Go to the section "Data"
4. Open the dimension properties
5. Activate "Show totals"

[Upali_Wijearatne]

I can not find options shown in your screen pls 

[steeefan]

Oh, you're using QlikView. Then proceed as follows:

1. Right click on the pivot table object
2. Properties
3. Presentation
4. For each dimension you want totals, select it and activate "Show Partial Sums"

[Upali_Wijearatne]

That I have already done But I won't total for each year 2022  & 2023  I get only grand total . That is my issue my friend

[steeefan]

In that order with that layout of the pivot table, it will not work. You can either try rearraning the dimensions or calculate these values in the script as well.

[marcus_sommer]

Principally you will need dimensional values/layers if you want to display any calculation-results. This means further if they don't exists within the origin data they need to be created respectively enabled. Enabling dimensional layers means to enable the totals respectively partial sums within the object-properties for the relevant dimensions.

Partial sums alone won't return your wanted values but you could query the layer with dimensionality() and branching then your calculation, maybe for year-growth with something like this:

if(dimensionality() = 2, sum(X) / sum(Y), sum(Z))

In regard for the products you may add another product-grouping field (maybe also an artificial one) to get an extra layer for the products.

Alternatively to the use of partial sums feature you could add the missing field-values of growth and total to the relevant fields - you may double the fields in the script to have them in the origin way and with the extra information to be used within the pivot.

Beside this you may also replace some of dimensional layer with expressions, for example by removing the year-field and applying 3 expressions, one for each year and then the growth-calculation.

Further quite often it's useful to combine the above hinted approaches to get the most suitable view. 

[Upali_Wijearatne]

Thanks for your reply. But I am unable comprehend it if you can provide me some script much appreciated