Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
HendrikH
Contributor
Contributor

Calculate Total of Pivot Tabel Columns and Show it in Each Cell

Hello,

I am working with pivot table and want to set values NULL in dependents of the total of the columns. The dimensions of my table are the color and size of products. In my approach I try to

  1. calculate the total by myself...
  2. ... and make it accessible in each cell of the column.  
  1. works fine, but I fail at 2.

The formula in the pivot table is

sum(Sales)/RangeSum(First(Sum(Sales), 1, NoOfColumns()))

and I calculate the total as

sum(aggr(sum(Sales), Sizes))/sum(total Sales)

However I end up with the following

 

pivotproblem.PNG

 

 

 

The marked is a pivot table to demonstrate my problem and contains my custom calculated total values. The bottom table I my actual pivot table with totals in bold text. As one can see I calculate the totals correctly but they are somehow randomly distributed in terms of row location.

Depending on the column I want the same value for each cell. For example, the correct values for 32/32 in the orange table are 0.03, 0.03 and not 0.00, 0.03.

I there a method to provid the totals to each cell? Is there a smater way to set columns to Null if the total is <=0.01?

 

Edit:

I updated the formula for the pivot table, because I forgot that I use RangeSum(First(Sum(Sales),1,NoOfColumns())) in the dominator and not just sum(total Sales).

8 Replies
sunny_talwar

May be this

Sum(TOTAL <Size> Sales)/Sum(TOTAL Sales)

 

HendrikH
Contributor
Contributor
Author

@sunny_talwarunfortunately your formula is a total "too much" and the set analysis does not work as desired:

pivotproblem2.PNG

sunny_talwar

Size is one of the dimension within the chart?

HendrikH
Contributor
Contributor
Author

@sunny_talwar The dimensions are text(Size) for the columns and Color for the rows, but using <text(Size)> in the set analysis throws an error.

sunny_talwar

May be you need square brackets

<[text(Size)]>

 

HendrikH
Contributor
Contributor
Author

@sunny_talwar  Unfortunatelys, the formula

sum(total <[text(SIZE)]> Sales)/sum(total Sales)

throws an error as well.

sunny_talwar

What error message do you see?

Qlik-A-Holic
Partner - Contributor II
Partner - Contributor II

Hendrik,

it's not helping monday ,but it doesn't matter 🙂

let me help you.

to get the same calculation is each cell you easily take the formular:

sum(total <GROESSE_CLEANED> ABSATZ_STK)/sum(total ABSATZ_STK)

Hope it helps,

 

Cheers,

 

Chris