topic Re: Follow-up question Column totals in PivotTable in QlikView

Follow-up question Column totals in PivotTable

pacoli2013 — Tue, 29 Aug 2017 11:29:37 GMT

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

sunny_talwar — Tue, 29 Aug 2017 12:04:32 GMT

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

Re: Follow-up question Column totals in PivotTable

pacoli2013 — Tue, 29 Aug 2017 15:08:50 GMT

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

sunny_talwar — Tue, 29 Aug 2017 15:36:43 GMT

I see you new thread, but not sure where in the qvw do you see 100%?

files belonging to earlier discussions

Re: Follow-up question Column totals in PivotTable

pacoli2013 — Wed, 30 Aug 2017 15:16:31 GMT

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

sunny_talwar — Wed, 30 Aug 2017 19:15:56 GMT

May be try this

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

Re: Follow-up question Column totals in PivotTable

pacoli2013 — Thu, 31 Aug 2017 07:06:13 GMT

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

sunny_talwar — Thu, 31 Aug 2017 09:00:02 GMT

‌How about this

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

Re: Follow-up question Column totals in PivotTable

pacoli2013 — Thu, 31 Aug 2017 12:51:56 GMT

Hello Sunny,

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

Regards Court

Re: Follow-up question Column totals in PivotTable

sunny_talwar — Thu, 31 Aug 2017 12:53:44 GMT

That's awesome... Please close the thread by marking correct and helpful responses...

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

P.S. Make sure you mark those responses which actually helped you and not your own responses....