Total not right in pivot chart

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a pivot table and it must stay a pivot table that I can not get the total calculations on completed right and because of that the compliance is also wrong. So for this example we are looking at just one location (600) and the month of May for 2017. everything else is right except the ones in red at the bottom.

Location	Date	SKU Count	Completed	Possible	Compliance
600	5/1/2017	35	1	1	100%
600	5/2/2017	35	1	1	100%
600	5/3/2017	35	1	1	100%
600	5/4/2017	35	1	1	100%
600	5/5/2017	35	1	1	100%
600	5/8/2017	35	1	1	100%
600	5/9/2017	35	1	1	100%
600	5/10/2017	35	1	1	100%
600	5/11/2017	35	1	1	100%
600	5/12/2017	35	1	1	100%
600	5/15/2017	35	1	1	100%
600	5/16/2017	35	1	1	100%
600	5/17/2017	35	1	1	100%
600	5/18/2017	0	0	1	0.00%
600	5/19/2017	35	1	1	100%
600	5/22/2017	0	0	1	0.00%
600	5/23/2017	35	1	1	100%
600	5/24/2017	35	1	1	100%
600	5/25/2017	35	1	1	100%
600	5/26/2017	35	1	1	100%
600	5/29/2017	0	0	1	0.00%
600	5/30/2017	0	0	1	0.00%
600	5/31/2017	35	1	1	100%
TOTAL		664	1	23	4.35%

Here is the calculations for each row.

Location is a Dimension

Date is a Dimension

SKU Count = count({<[Type]={'Cycle'}>} DISTINCT [Prd Number])

Completed = if([SKU Count] > 0, 1, 0)

Possible =

Sum(

if (WeekDay([Date]) = 'Mon' and Monday = 'X', 1 ,

if (WeekDay([Date]) = 'Tue' and Tuesday = 'X', 1 ,

if (WeekDay([Date]) = 'Wed' and Wednesday = 'X', 1 ,

if (WeekDay([Date]) = 'Thu' and Thursday = 'X', 1 ,

if (WeekDay([Date]) = 'Fri' and Friday = 'X', 1 , 0 )))))

)

Compliance =

if(Possible = '0' and Completed = '1', '0',

if(Possible = '0' and Completed = '0', '1',

( [Completed] / [Possible])))

Now all the other calculations work and compliance should work if I could get the completed to work correctly. For Completed I have tried wrapping it with SUM() and COUNT() and both did not work.

What can I do to get the completed total to be the correct number (19) rather then 1.

I have to have the chart as a pivot table I can not change it to any other chart.

Re: Total not right in pivot chart

Gysbert_Wassenaar — Fri, 04 Aug 2017 16:34:51 GMT

Completed = sum(aggr(if( count({<[Type]={'Cycle'}>} DISTINCT [Prd Number]) > 0, 1, 0),Location,Date))

Re: Total not right in pivot chart

Anonymous — Fri, 04 Aug 2017 16:39:38 GMT

Thank you so very much! That worked.

topic Re: Total not right in pivot chart in QlikView

Total not right in pivot chart

Re: Total not right in pivot chart

Re: Total not right in pivot chart