topic Re: Pivot Table in App Development

Pivot Table

Upali_Wijearatne — Fri, 05 Jan 2024 07:07:50 GMT

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.

Re: Pivot Table

steeefan — Fri, 05 Jan 2024 08:00:43 GMT

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.

Re: Pivot Table

Upali_Wijearatne — Fri, 05 Jan 2024 09:40:48 GMT

Thanks

Can you pls elaborate how to activate show total value please

Re: Pivot Table

steeefan — Fri, 05 Jan 2024 10:11:04 GMT

Certainly:

Switch to Edit mode
Select your pivot table
Go to the section "Data"
Open the dimension properties
Activate "Show totals"

Re: Pivot Table

Upali_Wijearatne — Fri, 05 Jan 2024 10:31:30 GMT

I can not find options shown in your screen pls

Re: Pivot Table

steeefan — Fri, 05 Jan 2024 11:42:20 GMT

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

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

Re: Pivot Table

Upali_Wijearatne — Fri, 05 Jan 2024 12:08:31 GMT

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

Re: Pivot Table

steeefan — Fri, 05 Jan 2024 12:10:47 GMT

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.

Re: Pivot Table

marcus_sommer — Mon, 08 Jan 2024 10:26:19 GMT

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.

Re: Pivot Table

Upali_Wijearatne — Mon, 08 Jan 2024 13:59:43 GMT

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

Re: Pivot Table

marcus_sommer — Mon, 08 Jan 2024 14:22:15 GMT

Within the script you may add the following load-statement:

concatenate(MyProductTable) load 'Total' as Product autogenerate 1;

which extends the product-field.

Further enable all partial sums within the pivot and add two new expressions of:

dimensionality()

secondarydimensionality()

which will return the layer-order of all included dimensions. These order could be queried for your real calculation to branch appropriately.

Like already hinted the replacement of the Year with expressions could be simplify the matter which would require at least 3 expressions with something like:

sum({< Year = {2022}>} Value)
sum({< Year = {2023}>} Value)
sum({< Year = {2023}>} Value) / sum({< Year = {2022}>} Value) - 1

Just play a bit with the examples and you will soon get the wanted information.