topic Re: Partial Sum Incorrect in Pivot Table with multiple dimensions included in QlikView

Partial Sum Incorrect in Pivot Table with multiple dimensions included

Wed, 28 Oct 2015 13:46:23 GMT

Hi All,

I have searched the community forums for the related answers and implemented in my expressions but they are not working, so thought of posting my requirement and get the help from qlikview experts.

My requirement is that i have created a Pivot table which has got 4 static dimensions, 1 cyclic dimension , 3 expressions

(Among these 4 static dimensions one dimension is shown by 'Enabling condition') and i am showing the data with Partial sum.

Now the problem happens here as shown to display "Total"

If the expression is calculated using SUM like SUM(Sales)-- it works Fine

If the expression is calculated using COUNT(DISTINCT ) like Count(Distinct Orders)-- it DOESN'T works Fine

This is the theoritical explanation for my requirement, so i am sharing an application using AdventureWorks as my data source

The calculated dimensions used in the Application are just for reference, in my original apllication i have got Year and Month fields

Please help me out !! !

Re: Partial Sum Incorrect in Pivot Table with multiple dimensions included

Gysbert_Wassenaar — Wed, 28 Oct 2015 17:19:11 GMT

What do you want to see as the partial sum? The distinct count of orders for the dimension value or the sum of the counts of the detail records? The first is what you have and it will be correct. The second requires using the aggr function:

sum(aggr(count(distinct Orders), MyDim1, MyDim2, ..., MyDimX))

where MyDim1...MyDimX are the dimensions of your pivot table.

Re: Partial Sum Incorrect in Pivot Table with multiple dimensions included

Thu, 29 Oct 2015 05:13:39 GMT

Hi Gysbert Wassenaar,

Thanks for your time, I have seen this function being suggested by the Community members in the other discussions and so i have used the same formula as suggested like

sum(aggr(count(distinct Orders), MyDim1, MyDim2, ..., MyDimX))

So in my case i have written expression like below

=IF(GetCurrentField([Cat/Prod]) = 'EnglishProductName',

sum(aggr(count(distinct Orders),Category,$(=GetCurrentFieldNamae(GroupName)),Year,Month),

sum(aggr(count(distinct Orders),Category,$(=GetCurrentFieldNamae(GroupName)),EnglishProductName,Year,Month)

)

But still the issue is with the 'TOTAL' which is not displaying correct based on the change of my cyclic dimension row values