Incorrect Total in a Pivot chart

[fashid]

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

[Gysbert_Wassenaar]

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

[sunny_talwar]

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

[fashid]

Hi,

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

[sunny_talwar]

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)

/

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

[fashid]

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

[sunny_talwar]

You can remove any expression you want now

[sunny_talwar]

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

[fashid]

I do not want to remove expressions.

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

Would it be possible ???

[sunny_talwar]

PFA

Best,

Sunny