Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

pacoli2013
Contributor

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

 

9 Replies

Re: Follow-up question Column totals in PivotTable

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

pacoli2013
Contributor

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

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

files belonging to earlier discussions

pacoli2013
Contributor

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)

pacoli2013
Contributor

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 monthTotal AmountSalesShare in %Share with LineAmountSalesShare in %Share with LineAmount
Total 7450,0024%0,0039%926.4512,9562%4,8202%
Jan 1.981.1542940,0009%0,0015%95.6780,3053%0,4978%
Feb 2.336.501---125.5280,4005%0,6531%
Mrz 2.840.673---186.4460,5949%0,9701%
Apr 2.075.382220,0001%0,0001%90.1610,2877%0,4691%
Mai 2.489.147---149.0140,4755%0,7753%
Jun 2.331.249---94.7490,3023%0,4930%
Jul 2.654.750---100.6500,3212%0,5237%
Aug 2.511.1254300,0014%0,0022%84.2260,2688%0,4382%
MonthsAmounts% from Total Amount% from Total LineAmount
Jan1.981.1546,3216%10,3078%
Feb2.336.5017,4554%12,1566%
Mrz2.840.6739,0642%14,7798%
Apr2.075.3826,6222%10,7980%
Mai2.489.1477,9425%12,9508%
Jun2.331.2497,4387%12,1293%
Jul2.654.7508,4709%13,8124%
Aug2.511.1258,0126%13,0652%
19.219.98161,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)

pacoli2013
Contributor

Re: Follow-up question Column totals in PivotTable

Hello Sunny,

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

Regards Court

Re: Follow-up question Column totals in PivotTable

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....

Community Browser