topic Re: Need 'Partial Sums', when Dim has both Count & Sum in QlikView

Need 'Partial Sums', when Dim has both Count & Sum

Saravanan_Desingh — Thu, 24 Mar 2016 17:03:21 GMT

I have a situation.

My Calculated Dimension is as follows (Using a Field & Text):

=Pick(Dim,[Strategic Location],'Open Req')

My Expression is (having both Count & Sum):

If(Dim=1,Count($(vAggrFld)),Sum(OpenReq))

This is Pivot Table. If I select 'Show Partial Sums' in the Presentation tab, I'm getting only the Sum values not the Sum of Rows.

I'm looking something similar to Straight Table - Sum of rows

Below Expression is also not working..

RangeSum(If(Dim=1,Count($(vAggrFld)),Sum(OpenReq)))

Can you please help me? Thank you.

Re: Need 'Partial Sums', when Dim has both Count & Sum

sunny_talwar — Thu, 24 Mar 2016 17:05:38 GMT

Not sure if I understand the requirement completely. Do you have a sample you can share?

Re: Need 'Partial Sums', when Dim has both Count & Sum

Saravanan_Desingh — Thu, 24 Mar 2016 17:09:33 GMT

Hi Sunny,

Please see that attachment.I'm expecting the 'Total HC' as 1341, which is sum of all rows.

But I'm getting only 72, which is sum of the OpenReq Rows only.

Re: Need 'Partial Sums', when Dim has both Count & Sum

swuehl — Thu, 24 Mar 2016 17:33:28 GMT

Try

=If(Dimensionality=0,

Sum( Aggr( If(Dim=1,Count($(vAggrFld)),Sum(OpenReq)), Dim)),

If(Dim=1,Count($(vAggrFld)),Sum(OpenReq))

)

[in general, sum-of-rows in pivot tables can be achieved with advanced aggregation. Here, your setting with calculated dimensions is a bit more complex, so if above doesn't work, please post a small sample QVW.]

Re: Need 'Partial Sums', when Dim has both Count & Sum

Saravanan_Desingh — Thu, 24 Mar 2016 17:41:57 GMT

Hi Stefan,

Thanks for your response. But the expression didn't work.

Being inspired by your code, I did the below expression and it worked.

Thanks again.

If(Dimensionality()<>0,
If(Dim=1,Count($(vAggrFld)),Sum(OpenReq))
,RangeSum(Count($(vAggrFld)),Sum(OpenReq))
)

Re: Need 'Partial Sums', when Dim has both Count & Sum

Saravanan_Desingh — Thu, 24 Mar 2016 17:45:02 GMT

Hi Stefan,

I sincerely sorry.

I just missed the () in the Dimensionality.

Your code is working. Sorry again.

=If(Dimensionality()=0,

Sum( Aggr( If(Dim=1,Count($(vAggrFld)),Sum(OpenReq)), Dim)),

If(Dim=1,Count($(vAggrFld)),Sum(OpenReq))

)

Re: Need 'Partial Sums', when Dim has both Count & Sum

swuehl — Thu, 24 Mar 2016 17:46:34 GMT

No, that was me

Glad that it worked. Your solution looks a bit more simple, though.

Re: Need 'Partial Sums', when Dim has both Count & Sum

Saravanan_Desingh — Thu, 24 Mar 2016 17:47:11 GMT

Thanks, Stefan ..