topic Re: Year and Month wise data not matching in QlikView

Year and Month wise data not matching

amit_saini — Wed, 01 Jul 2015 11:03:09 GMT

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,

Re: Year and Month wise data not matching

sunny_talwar — Wed, 01 Jul 2015 11:06:14 GMT

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

Re: Year and Month wise data not matching

sunny_talwar — Wed, 01 Jul 2015 11:08:06 GMT

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))

Re: Year and Month wise data not matching

amit_saini — Wed, 01 Jul 2015 11:18:39 GMT

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,

Re: Year and Month wise data not matching

awhitfield — Wed, 01 Jul 2015 11:24:24 GMT

HI Amit,

are you able to upload your 'qvw?

Andy

Re: Year and Month wise data not matching

sunny_talwar — Wed, 01 Jul 2015 11:24:51 GMT

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?

Re: Year and Month wise data not matching

ashwanin — Wed, 01 Jul 2015 11:26:09 GMT

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.

Re: Year and Month wise data not matching

sunny_talwar — Wed, 01 Jul 2015 11:26:32 GMT

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

Re: Year and Month wise data not matching

amit_saini — Wed, 01 Jul 2015 11:41:20 GMT

Hi Sunny,

Understand .

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

Re: Year and Month wise data not matching

amit_saini — Wed, 01 Jul 2015 11:41:35 GMT

Yes Andy

Re: Year and Month wise data not matching

sunny_talwar — Wed, 01 Jul 2015 11:42:07 GMT

Would you be able to share a sample?

Re: Year and Month wise data not matching

sunny_talwar — Wed, 01 Jul 2015 11:44:25 GMT

Are you not getting any results? or the results you are getting are not right:

Try this as well:

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

Re: Year and Month wise data not matching

amit_saini — Wed, 01 Jul 2015 12:20:21 GMT

PFA

Re: Year and Month wise data not matching

sunny_talwar — Wed, 01 Jul 2015 12:28:01 GMT

Wow, this is huge

Re: Year and Month wise data not matching

sunny_talwar — Wed, 01 Jul 2015 12:46:29 GMT

Here, this formula seems to work:

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

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

But this is aggregating on the Month Column and in your case I would try to aggregate on a unique column. What is a unique key in your fact table?