9 Replies Latest reply: Aug 31, 2017 8:53 AM by Sunny Talwar

# Follow-up question Column totals in PivotTable

Hello

My first question about Column totals in PivotTable was answered by Sunny Talwar and helped me a lot. Thanks for that Sunny.

In that PivotTable I have two expressions are Sum(LineAmount) and Sum(LineAmount)/Sum(TotalAmount)

Problem is that Sum(LineAmount)/Sum(TotalAmount) gives always 100%.

Cause
is that LineAmount and Total Amount come from the same table and Total Amount is in fact Sum(LineAmount)

Creating a new table with only dates and amounts didn't help a lot. And I hope someone can help with this problem.

For data and other information, I refer to the first question I posted: Column totals in PivotTable in QlikView Layout & Visualizations

Thanks in advance

Regards Court

• ###### Re: Follow-up question Column totals in PivotTable

But the sample from other thread don't show 100%? Where are you seeing 100%?

• ###### Re: Follow-up question Column totals in PivotTable

Hello Sunny,

the Problem is when I create a table with only dates and amounts, my total amount per timefield is bigger as the amount been calculated in the Dimension (= Num(Aggr(Sum(LineAmount), \$(=GetCurrentField([Time Group]))),' #.##0'))

So now I don't now what calculation is right

I cann't attach new file here so I will add them to a new discussion

Hope to hear from you soon

Regards Court

• ###### Re: Follow-up question Column totals in PivotTable

Hello Sunny,

I have analysed my PivotTable and Data. I have two Data Sets or tables loaded in my script: SalesInvLineShippingDates and  TableTotalAmount

My Pivottables has the dimensions : timegroup, the calculated dimension (I got from you) Num(Aggr(Sum(LineAmount), \$(=GetCurrentField([Time Group]))),' #.##0') and the third dimension is DescriptionRequested.

My two expressions are Sum(LineAmount) and Sum(LineAmount)/Sum(TotalAmount)

I have also a filter Companies.

Dimensions and Expressions work well for all the companies together. There is no direct relation between the both tables. They are connected with my DayGrid/Calendar using teh field Date.

The Filter Companies works with the dimensions and the first Expression because Company is a field from the table SalesInvLineShippingDates. Company is not a field in the other table, result is dat TotalAmount is allways the total for all companies. When I create a keyfield Company in my TotalTable. both expressions react, but then

Sum(LineAmount) = Sum(TotalAmount) for every interval.

My idea is that a part of thee Calculated Dimension should come in place of TotalAmount in the expression

I tried: Sum(LineAmount)/Aggr(Sum(LineAmount), \$(=GetCurrentField([Time Group]))) but that didn't work.

My Knowledge of Set Analysis is not so good or big that I manage that.

Hope you can help

Thanks in advance

Regards Court

• ###### Re: Follow-up question Column totals in PivotTable

May be try this

Sum(LineAmount)/Sum(TOTAL <Company> TotalAmount)

• ###### Re: Follow-up question Column totals in PivotTable

Hello Sunny,

First thanks you for your Support. I'll think the solution is almost there.

 Interval -999999 -999999 -999999 < -14 Tage < -14 Tage < -14 Tage Booking month Total Amount Sales Share in % Share with LineAmount Sales Share in % Share with LineAmount Total 745 0,0024% 0,0039% 926.451 2,9562% 4,8202% Jan 1.981.154 294 0,0009% 0,0015% 95.678 0,3053% 0,4978% Feb 2.336.501 - - - 125.528 0,4005% 0,6531% Mrz 2.840.673 - - - 186.446 0,5949% 0,9701% Apr 2.075.382 22 0,0001% 0,0001% 90.161 0,2877% 0,4691% Mai 2.489.147 - - - 149.014 0,4755% 0,7753% Jun 2.331.249 - - - 94.749 0,3023% 0,4930% Jul 2.654.750 - - - 100.650 0,3212% 0,5237% Aug 2.511.125 430 0,0014% 0,0022% 84.226 0,2688% 0,4382% Months Amounts % from Total Amount % from Total LineAmount Jan 1.981.154 6,3216% 10,3078% Feb 2.336.501 7,4554% 12,1566% Mrz 2.840.673 9,0642% 14,7798% Apr 2.075.382 6,6222% 10,7980% Mai 2.489.147 7,9425% 12,9508% Jun 2.331.249 7,4387% 12,1293% Jul 2.654.750 8,4709% 13,8124% Aug 2.511.125 8,0126% 13,0652% 19.219.981 61,3281% 100,0000%

The Shares should not be calculated with  the total amount 19.219.981 but by the total per month/date or week (= Time Group)  for instance 294/1981154 or 186446/2840673

I tried Sum(LineAmount)/Sum(TOTAL <Company> LineAmount)

but that is not the total solution. So I think in the Expression the Time Group should also be build in. But how or if that is possible, I don't know.

Regards Court

• ###### Re: Follow-up question Column totals in PivotTable

How about this

Sum(LineAmount)/Sum(TOTAL <\$(=GetCurrentField([Time Group]))> LineAmount)

• ###### Re: Follow-up question Column totals in PivotTable

Hello Sunny,

It's is the solution I was looking for. Thanks

Regards Court