Value changes when a dimension is added

[Anonymous]

Hi

I have a graph which gives the correct value when single dimension is added , if i add a new dimension the value changes , could anybody please explain why this happens and also how i can handle this without changing the values of utilrate when operationname is added as dimension , (please look into the sample data.qvw)

Thank you

[johnw]

Let me just give a very quick example of what I think your problem is. Here are a couple tables.

F1, F2
A, X
A, Y
B, X

F2, F3
X, 10
Y, 20

And here's a resulting chart:

F1 sum(F3)
A 30
B 10
Total 30

The total of 30 in that chart is equivalent to what you see when you don't have OPERATION_NAME in your chart. The separate rows of 30 and 10, which would create a bar of height 40, correspond to what you see when you DO have OPERATION_NAME in your chart.

Can you see why you can see two different totals, both 30 and 40? Can you see why, in a sense, both totals are correct?

Assuming you want the 40 total even when you don't have F1 as a dimension, the equivalent for your chart would be this expression:

sum(aggr(sum(USED_HOURS)/sum(AVAILABLE_HOURS),SUN_WEEK_START_DATE,OPERATION_NAME))

But I'm guessing that you really want the 30 total (in your chart, 25% total). And frankly, I don't think there's a way to show that in a stacked bar chart. In my example, if A is 30, which it is, and if you want the total height of the bar to be 30, how exactly are you to show that B is 10? If you stack on B, your total will be wrong. If you don't, you can't see the data for B. If you reduce A to 20 to make B fit, then A is wrong. So if you somehow want to display in stacked bar graph form that 30 + 10 = 30, you're asking for the impossible.

I may, of course, have misunderstood either the problem itself or what you want as a solution.

[#3]

Hi, (hi John)

I took a look at the application and your data. I had some trouble with your data modell in some details. You are mixing fields into the two tables that seem to me to belong together. EG Available.TESTER_NAME, TESTER_KEY and Used.TESTER should be together in one dimension-table. Same with the date_fields. And OPERATION_NAME is only in Used. In consequence I think the link between your two tables isn't quite valid.

With this in mind I rebuild the data modell using a star schema. Please take a look at it and tell me what do you think about it.

Regards, Roland

[Anonymous]

Hi ,

Thank you for the responses...

Yes i dont have Operation Name in AvailableHours table..it is there only in used hours , its something like i have each tester available hours for each day .And i have each tester's Used hours for each day by each operation name.

Thanks!

[Anonymous]

any help with this , do you think i can have something changed in the scripts in order to make this work ,

and John i did not get why the total is 30 in the results for the chart 😞

[johnw]

RAMYA_BEGINNER wrote:i did not get why the total is 30 in the results for the chart

The chart has expression sum(F3). There are two rows in the table that has F3 in it. The values on those rows are 10 and 20. Therefore, sum(F3) is 10 + 20 = 30. The chart doesn't care that you have a dimension of F1 when doing the total. That dimension is ignored when doing the total line. You can force it to pay attention to F1 with a sum(aggr(sum(F3),F1)). Or you can handle it by setting the total mode to "sum of rows".

[Anonymous]

Hi John

Thank you , now i got it.......

could you please suggest something on this...Is there any way where i can get this?

Here is my current chart:

Week	Operation	Sum (Available)	Sum (Used)	Util Rate
		10,752	6776.05	63%
12/12/2010	ENGINEERING	7,257	6131.52	84%
12/12/2010	MAINTENANCE	828	559.27	68%
12/12/2010	PRODUCTION	154	85.27	55%
12/12/2010	-	2,942	0.00	0%

Expected output:

Week	Operation	Sum Available	Sum Used	Util Rate
		10,752	6776.05	63%
12/12/2010	ENGINEERING	10,752	6131.52	57%
12/12/2010	MAINTENANCE	10,752	559.27	5%
12/12/2010	PRODUCTION	10,752	85.27	1%
12/12/2010	-	10,752	0.00

Thank you....

[johnw]

Probably this:

Sum Available = sum(total AVAILABLE_HOURS)
Sum Used = sum(USED_HOURS)
Util Rate = "Sum Available"/"Sum Used"

But since your example doesn't have that data or that chart, I can't verify.

[Anonymous]

Thank you so much John , you just have all the answers for my questions always , i just used total , and it worked somehow i dont find the the total in the drop down box i have got for aggregation , but when i write Total in the expression it worked , here is my result set now,

Week	Operation	Sum (total(AVAILABLE_HOURS))	Sum (USED_HOURS)	Sum (USED_HOURS)/sum(total(AVAILABLE_HOURS))
		10752.00	6776.05	63%
12/12/2010	ENGINEERING	10752.00	6131.52	57%
12/12/2010	MAINTENANCE	10752.00	559.27	5%
12/12/2010	PRODUCTION	10752.00	85.27	1%
12/12/2010	-	10752.00	0.00	0%

Could you please also let me know how do i get Idle% 37% in the chart , basically 100 - 63 = 37 .

[Anonymous]

Thank you so much John , you just have all the answers for my questions always , i just used total , and it worked somehow i dont find the the total in the drop down box i have got for aggregation , but when i write Total in the expression it worked , here is my result set now,

Week	Operation	Sum (total(AVAILABLE_HOURS))	Sum (USED_HOURS)	Sum (USED_HOURS)/sum(total(AVAILABLE_HOURS))
		10752.00	6776.05	63%
12/12/2010	ENGINEERING	10752.00	6131.52	57%
12/12/2010	MAINTENANCE	10752.00	559.27	5%
12/12/2010	PRODUCTION	10752.00	85.27	1%
12/12/2010	-	10752.00	0.00	0%

Could you please also let me know how do i get Idle% 37% in the chart , basically 100% - 63% = 37% .