topic Re: Chart data not showing up correctly, I used the same expression from pivot table which shows correct data in QlikView

Chart data not showing up correctly, I used the same expression from pivot table which shows correct data

Wed, 03 Aug 2016 21:09:26 GMT

I used the same expression from pivot table which shows correct data, but the chart data is incorrect.

For example, in Qlikview the weighted total from my pivot is showing $64954,

whereas when I copied/pasted the same expression into chart =num(sum(IF(TODAY()>[Opp Exp Decision Date], ([Local Member Firm Fees]*[Opp Probability]/100)/295*0.5,[Local Member Firm Fees]*[Opp Probability]/100/295)/IF(((([Opp Est Eng End Date]-[Opp Est Eng Start Date])/30)=0),1,([Opp Est Eng End Date]-[Opp Est Eng Start Date])/30)),'#,###.'), the incorrect value 1,332,827 shows up.

Thanks for your help.

Re: Chart data not showing up correctly, I used the same expression from pivot table which shows correct data

sunny_talwar — Wed, 03 Aug 2016 22:22:34 GMT

I guess where did you come uo with the value $64954? I don't see that value in the Weighted Total column. Would it be possible to share a sample to look at?

Best,

Sunny

Re: Chart data not showing up correctly, I used the same expression from pivot table which shows correct data

Thu, 04 Aug 2016 02:51:12 GMT

64954 is the sum of these four rows (277,044, 337,500, 10000, 25000).

what are some possible reasons that the data found in chart is different from the pivot table even though their expressions are the same.

Re: Chart data not showing up correctly, I used the same expression from pivot table which shows correct data

sunny_talwar — Thu, 04 Aug 2016 10:21:44 GMT

One of the biggest reason is the if statement takes place dimension wise. For some dimensions, your if statement may return true or false, but for another one it would do something else. What is the dimension or dimensions from the table above? May be try this with Aggr Function:

=Num(

Sum(Aggr(

Sum(If(Today() > [Opp Exp Decision Date], ([Local Member Firm Fees]*[Opp Probability]/100)/295*0.5, [Local Member Firm Fees] * [Opp Probability]/100/295)/If(((([Opp Est Eng End Date]-[Opp Est Eng Start Date])/30)=0),1,([Opp Est Eng End Date]-[Opp Est Eng Start Date])/30)),

DimensionsFromYourTableHereSeparatedByComma)),

,'#,###.')

This DimensionsFromYourTableHereSeparatedByComma is a placeholder and you need to replace this with the dimensions you have in your table above.

Re: Chart data not showing up correctly, I used the same expression from pivot table which shows correct data

Thu, 04 Aug 2016 14:53:42 GMT

Hi,

are you referring to the dimension I used from the pivot table?

I am not too sure what to do here.

Re: Chart data not showing up correctly, I used the same expression from pivot table which shows correct data

sunny_talwar — Thu, 04 Aug 2016 15:13:02 GMT

Yes the dimensions from your pivot table

Re: Chart data not showing up correctly, I used the same expression from pivot table which shows correct data

Thu, 04 Aug 2016 15:32:51 GMT

Hi,

I figured out the reason. The calculation in pivot table is actually off.

I am trying to divide this expression, numerator: =IF(TODAY()>[Opp Exp Decision Date], ([Local Member Firm Fees]*[Opp Probability]/100)/295*0.5,[Local Member Firm Fees]*[Opp Probability]/100/295)

with denominator: =IF(((([Opp Est Eng End Date]-[Opp Est Eng Start Date])/30)=0),1,([Opp Est Eng End Date]-[Opp Est Eng Start Date])/30)

Can somebody please guide me on how to link the two with division?