topic Re: Partial sum not working on pivot table in QlikView

Partial sum not working on pivot table

ranibosch — Wed, 30 May 2018 08:11:03 GMT

good day,

does anyone know why my partial sum on the pivot table does not want to show?

Re: Partial sum not working on pivot table

sunny_talwar — Wed, 30 May 2018 09:03:28 GMT

What is your expression that you are using?

Re: Partial sum not working on pivot table

olivierrobin — Wed, 30 May 2018 09:34:24 GMT

hello

maybe because one of the rows contains null in the value you are using

and the grouping function used returns null if one of the value is null

Re: Partial sum not working on pivot table

ogautier62 — Wed, 30 May 2018 09:58:10 GMT

Hi,

can you add selection boxes on these fields to see if there is any data or not ?

Re: Partial sum not working on pivot table

ranibosch — Fri, 01 Jun 2018 10:37:09 GMT

Hi Sunny,

my expressions are as follows (i have 4):

1. Average monthly forecast (cases):

if(IsNull

(sum ([Annual Sales])*-1/12 * (1 + ((sum([3 Months Sales]) - sum([Prior Year 3 Months Sales]))

sum([Prior Year 3 Months Sales])))

[FG Case weight])

(sum([3 Months Sales])*-1/3) / [FG Case weight]

(sum ([Annual Sales])*-1/12 * (1 + ((sum([3 Months Sales]) - sum([Prior Year 3 Months Sales]))

sum([Prior Year 3 Months Sales])))

[FG Case weight]) )

2. Number of pallets:

Ceil(

(if(IsNull

(sum ([Annual Sales])*-1/12 * (1 + ((sum([3 Months Sales]) - sum([Prior Year 3 Months Sales]))

sum([Prior Year 3 Months Sales])))

[FG Case weight])

(sum([3 Months Sales])*-1/3) / [FG Case weight]

(sum ([Annual Sales])*-1/12 * (1 + ((sum([3 Months Sales]) - sum([Prior Year 3 Months Sales]))

sum([Prior Year 3 Months Sales])))

[FG Case weight]) ) )

[FG Pallet Size]

)

3. Planned production (cases):

(Ceil(

(if(IsNull

(sum ([Annual Sales])*-1/12 * (1 + ((sum([3 Months Sales]) - sum([Prior Year 3 Months Sales]))

sum([Prior Year 3 Months Sales])))

[FG Case weight])

(sum([3 Months Sales])*-1/3) / [FG Case weight]

(sum ([Annual Sales])*-1/12 * (1 + ((sum([3 Months Sales]) - sum([Prior Year 3 Months Sales]))

sum([Prior Year 3 Months Sales])))

[FG Case weight]) ) )

[FG Pallet Size]

) )

* [FG Pallet Size]

4. Planned production (kg):

(Ceil(

(if(IsNull

(sum ([Annual Sales])*-1/12 * (1 + ((sum([3 Months Sales]) - sum([Prior Year 3 Months Sales]))

sum([Prior Year 3 Months Sales])))

[FG Case weight])

(sum([3 Months Sales])*-1/3) / [FG Case weight]

(sum ([Annual Sales])*-1/12 * (1 + ((sum([3 Months Sales]) - sum([Prior Year 3 Months Sales]))

sum([Prior Year 3 Months Sales])))

[FG Case weight]) ) )

[FG Pallet Size]

) )

* [FG Pallet Size]

*[FG Case weight]

Re: Partial sum not working on pivot table

sunny_talwar — Fri, 01 Jun 2018 11:07:59 GMT

I think all you might need is to surround your expression with Sum(Aggr(....., Dimensions))

http://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/NestedAggregations/sum-of-rows…

Re: Partial sum not working on pivot table

libinanto95 — Thu, 20 Feb 2020 10:04:01 GMT

But I have created Dimensions using Valuelist and Still I don't see the SUM.