topic Re: Pivot Partial Sum is not showing Subtotal in QlikView

Pivot Partial Sum is not showing Subtotal

Anonymous — Wed, 13 Dec 2017 02:41:50 GMT

Hi,

I am using Pivot table and trying to show "Subtotal" at the different Dimensions level. I am using Set Analysis in the expressions.

But I am not getting correct "Subtotal" count.

In the attached image, the last 4 colums are not showing the correct subtotal. When I expand the "Agent " column and see the data at "Alliance" level, the count which I should have got is different for all the last for fields. I am getting data cell by cell correctly. But subtotal is not working.

Here is the expressions for one field.

Expression for "Alliance Call Count from 11/01/2017 to 11/30/2017" field:

=sum( {<DIRECTOR_NAME=,MANAGER_NAME=,SUPERVISOR_NAME=,SALES_REP_CENTRAL_IDENTITY=,

IN_OUT_TRANSFER = {'INBOUND'}, %DIM_DATE_KEY= P({1< %DIM_DATE_KEY={">=$(vETEStartDateInput)<=$(vETEEndDateInput)"} >} %DIM_DATE_KEY) >} TOTAL <ALLIANCE_NAME> CALL_COUNT)

Here, I am ignoring all the fields except "Alliance" as I need "Alliance" level data here.

Again, I am using Date variable as I want to show the data for the selected date range.

But the Subtotal which I am getting is not correct. for this filed it should be "61804" but I am getting "187240" which is Subtotal of "Director" level.

I will really appreciate if anyone help me on that. Thanks.

Re: Pivot Partial Sum is not showing Subtotal

pradosh_thakur — Wed, 13 Dec 2017 03:42:41 GMT

1:Did you suppress null in any dimensions ? If yes than uncheck that and first than check your calculation. That sometime contain some value we miss.

2: Try this

=sum( {<DIRECTOR_NAME=,MANAGER_NAME=,SUPERVISOR_NAME=,SALES_REP_CENTRAL_IDENTITY=,

IN_OUT_TRANSFER = {'INBOUND'}, %DIM_DATE_KEY= P({1< %DIM_DATE_KEY={">=$(vETEStartDateInput)<=$(vETEEndDateInput)"} >} %DIM_DATE_KEY) >} CALL_COUNT)

Re: Pivot Partial Sum is not showing Subtotal

Anonymous — Wed, 13 Dec 2017 04:31:21 GMT

Hi Prakash,

Thanks for the reply. Yes, I suppressed the null value for "Director" dimension. But still I am getting same count for all.

which is "1067607" now.

Again, I tried the above expression, by removing Total <Alliance_Name> but in that case it is giving me the total count based on the "Agent Level" not "Alliance" level count.

In the above expression, ""Alliance Call Count from 11/01/2017 to 11/30/2017" field, I am trying to calculate the count based on the "Alliance level" by ignoring "Agent", but I want to show the total at "Agent" level.

Re: Pivot Partial Sum is not showing Subtotal

pradosh_thakur — Wed, 13 Dec 2017 06:52:58 GMT

Do you know the use of dimensionality function ? Try using that . If you can post a sample it would be easy for me to help . without a sample its like shooting the target in dark. It might hit might miss.

and Its Pradosh not prakash...

regards

Pradosh