Year and Month wise data not matching

[amit_saini]

Hi Folks,

I'm doing very simple calculation by using Year and Month as dimensions in bar chart , but my numbers is not matching.

Expression in both cases :

sum({< Defect_Description=, Complaint_Acceptance-={'4','5','6','7'}> }QTY_PPM*Top_Pos_Flag) / sum(QTY_Delivered_TOTAL) *1000000

Thanks,

AS

[sunny_talwar]

Are you that the chart on the right is for year 2015 only? May be it is showing the data for all years. Intead of using Months as dimensions I would use MonthYear to make sure that I am seeing Jan 2015, Feb 2015 and so on.

HTH

Best,

Sunny

[sunny_talwar]

Another thing I would try is to use this expression for your left table (year)

Sum(Aggr(Sum({< Defect_Description=, Complaint_Acceptance-={'4','5','6','7'}> }QTY_PPM*Top_Pos_Flag) / sum(QTY_Delivered_TOTAL) *1000000, Month))

Sum({<Defect_Description=, Complaint_Acceptance-={'4','5','6','7'}>} Aggr(Sum({< Defect_Description=, Complaint_Acceptance-={'4','5','6','7'}> }QTY_PPM*Top_Pos_Flag) / sum(QTY_Delivered_TOTAL) *1000000, Month))

[amit_saini]

Hi,

Sorry i checked even making Month-Year as dimension and your expression in second comment.

But I have a requirement where I need to call this expression in different graphs by using below Dimension:

-Year

-Month Graph

-Plant Graph

-Region Graph

-Customer Name Graph

and in all these graphs sum of data should be same .

Thanks,

AS

[awhitfield]

HI Amit,

are you able to upload your 'qvw?

Andy

[sunny_talwar]

The problem is that you are calculating two different sums and dividing them. When on different dimensions, you will get different values for the sums and when sums are different the division will never be the same

Dim1     Dim2     Value1     Value2

A          AA          30          4

A          BB          40          4

When Dimension is Dim1 Sum(Value1) = 70 and Sum(Value2) = 8 and there division is 70/8 = 8.75

When Dimension is Dim2 Sum(Value1) = 30, 40 and Sum(Value2) = 4,4 and there division is 30/4 + 40/4 = 17.5

Does that make sense?

[ashwanin]

Is Same formula used in both Graphs?

Can you please check the result by making a Group (cyclic or drill down)  of Month,year, plant and use that Group in one of the Graph, which seems to be fine and check whether the results vary or not.

[sunny_talwar]

Is there a unique field in your database on which you can aggr on:

Sum({<Defect_Description=, Complaint_Acceptance-={'4','5','6','7'}>} Aggr(Sum({< Defect_Description=, Complaint_Acceptance-={'4','5','6','7'}> }QTY_PPM*Top_Pos_Flag) / sum(QTY_Delivered_TOTAL) *1000000, UniqueField))

Try this expression for all your charts.

Best,

Sunny

[amit_saini]

Hi Sunny,

Understand .

But how can I achieve these graphs  ,  I tried solution below but sorry not working.

Sum({<Defect_Description=, Complaint_Acceptance-={'4','5','6','7'}>} Aggr(Sum({< Defect_Description=, Complaint_Acceptance-={'4','5','6','7'}> }QTY_PPM*Top_Pos_Flag) / sum(QTY_Delivered_TOTAL) *1000000, UniqueField))

[amit_saini]

Yes Andy