topic Re: Incorrect Total in a Pivot chart in QlikView

Incorrect Total in a Pivot chart

fashid — Thu, 30 Apr 2015 11:18:23 GMT

Hi,

I have created a pivot chart with a calculated dimension which is just a text.

I am getting wrong totals .

Is it possible to get the right total without using a straight table or will i have to use only the straight table.

I have attached the sample app

Regards,

Nadeem

Re: Incorrect Total in a Pivot chart

Gysbert_Wassenaar — Thu, 30 Apr 2015 11:52:05 GMT

Can you tell us what you think the total should be?

Re: Incorrect Total in a Pivot chart

sunny_talwar — Thu, 30 Apr 2015 12:16:28 GMT

Gysbert is right, until we won't know what's your expectation of the total we won't be able to much help. But general rule is to use something like this to get a correct total:

Sum/Count/Min/Max/Avg(Aggr(yourExpression, yourDimensions))

where the portion highlighted in red determines what your total needs to be.

HTH

Best,

Sunny

Re: Incorrect Total in a Pivot chart

fashid — Thu, 30 Apr 2015 16:31:13 GMT

Hi,

I Just need the total of the last column i.e. MTD score column which should be 107

Re: Incorrect Total in a Pivot chart

sunny_talwar — Thu, 30 Apr 2015 16:39:41 GMT

Try to use this expression for MTD Score's expression:

=Sum(Aggr(if((((18/100)* Count({<CalanderQuarter=,fYear=,FMonth=,LOCAL_CURRENCY=, REGION={'IND'},CONTROLLER={'WRITER'},SERVICE_LINE={'EXP','WCM','OMD','MSI'}, DateNum={">=$(=Num(MonthStart(today())))<=$(=vToday))"}>}DISTINCT ENQUIRY_ID)

sum({<CalanderQuarter=,fYear=,FMonth=,LOCAL_CURRENCY=,REGION={'IND'},CONTROLLER={'WRITER'},SERVICE_LINE={'EXP','WCM','OMD','MSI'},DateNum={">=$(=Num(MonthStart(today())))<=$(=vToday)"}>}E.TARGET))*100)>18,18,

(((18/100)*Count({<CalanderQuarter=,fYear=,FMonth=,LOCAL_CURRENCY=, REGION={'IND'},CONTROLLER={'WRITER'},SERVICE_LINE={'EXP','WCM','OMD','MSI'}, DateNum={">=$(=Num(MonthStart(today())))<=$(=vToday))"}>}DISTINCT ENQUIRY_ID)

Re: Incorrect Total in a Pivot chart

fashid — Thu, 30 Apr 2015 17:36:47 GMT

Hi,

Thanks alot it works but can i hide the total for the MTD % column as i do not need that.

Also why is it that i have to use AGGR in this case.

Regards,

Nadeem

Re: Incorrect Total in a Pivot chart

sunny_talwar — Thu, 30 Apr 2015 17:38:29 GMT

You can remove any expression you want now

Re: Incorrect Total in a Pivot chart

sunny_talwar — Thu, 30 Apr 2015 18:12:09 GMT

Aggregate is needed because of a missing functionality in Pivot table which is available in Straight table. Please find a image of the functionality which is available in Straight table:

So to get what can be done with a click in Straight table, you need to use a Aggregate function.

I hope this helps.

Best,

Sunny

Re: Incorrect Total in a Pivot chart

fashid — Thu, 30 Apr 2015 18:12:17 GMT

I do not want to remove expressions.

I just want to disable the totals for the MTD % column .

Would it be possible ???

Re: Incorrect Total in a Pivot chart

sunny_talwar — Thu, 30 Apr 2015 18:16:11 GMT

PFA

Best,

Sunny

Re: Incorrect Total in a Pivot chart

sunny_talwar — Thu, 30 Apr 2015 18:18:20 GMT

Disregard the last response. Look at the following attachment.

Best,

Sunny

Re: Incorrect Total in a Pivot chart

fashid — Thu, 30 Apr 2015 19:51:55 GMT

Hi Sunny,

You Have disabled the entire MTD% column.

The thing is that i want the MTD % column with all the values except the total .

Is it possible to keep totals of only a particular column in the pivot chart

Regards,

Nadeem

Re: Incorrect Total in a Pivot chart

sunny_talwar — Thu, 30 Apr 2015 19:58:44 GMT

All Columns are still here:

Re: Incorrect Total in a Pivot chart

fashid — Thu, 30 Apr 2015 20:58:58 GMT

Perfect but how did u just get the MTD score totals .

When i enabled the partial sums i am getting totals for the rest of the columns as well

Re: Incorrect Total in a Pivot chart

sunny_talwar — Thu, 30 Apr 2015 21:05:09 GMT

I used a concept of Dimensionality() to turn of the totals for all others except MTD Score.

Re: Incorrect Total in a Pivot chart

fashid — Thu, 30 Apr 2015 21:10:31 GMT

Many Thanks for Bearing with me and helping me solve this .

Learned a lot !!!!

Have a nice day !!

Rergards,

Nadeem

Re: Incorrect Total in a Pivot chart

sunny_talwar — Thu, 30 Apr 2015 21:15:06 GMT

‌we all are learning from each other my friend.

best,

Sunny