topic AW:Value changes when a dimension is added in QlikView

Value changes when a dimension is added

Anonymous — Fri, 18 Feb 2011 19:39:36 GMT

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

Value changes when a dimension is added

johnw — Sat, 19 Feb 2011 00:05:40 GMT

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.

AW:Value changes when a dimension is added

Sat, 19 Feb 2011 18:27:50 GMT

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

AW:Value changes when a dimension is added

Anonymous — Sat, 19 Feb 2011 23:02:14 GMT

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!

AW:Value changes when a dimension is added

Anonymous — Tue, 22 Feb 2011 19:14:51 GMT

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 😞

AW:Value changes when a dimension is added

johnw — Tue, 22 Feb 2011 19:37:57 GMT


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".

AW:Value changes when a dimension is added

Anonymous — Wed, 23 Feb 2011 01:06:59 GMT

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

AW:Value changes when a dimension is added

johnw — Wed, 23 Feb 2011 18:50:19 GMT

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.

AW:Value changes when a dimension is added

Anonymous — Wed, 23 Feb 2011 19:13:03 GMT

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 .

AW:Value changes when a dimension is added

Anonymous — Wed, 23 Feb 2011 19:13:03 GMT

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% .

AW:Value changes when a dimension is added

johnw — Wed, 23 Feb 2011 19:18:28 GMT

1 - sum(total USED_HOURS)/sum(total AVAILABLE_HOURS)

Total isn't an aggregation function, so it wouldn't show in a list of aggregation functions. It's... I don't know, a modifier to them? It should show up in the pop up help for the sum() function, though.

AW:Value changes when a dimension is added

Anonymous — Wed, 23 Feb 2011 19:28:29 GMT

Thank you John ,

Now i need to display the same in the bar chart as dimension % by each operation name and idle % , i remember you have suggested me in set analysis to display something like this , and i am following the same by using the below expression , Could you please see if my expression is correct ? Please bare with me , i am writing the set analysis first time by myself.

ENGINEERING % =sum({<OPERATION_NAME*={'ENGINEERING'}>}(sum(USED_HOURS)/sum(total(AVAILABLE_HOURS))))

PRODUCTION % =sum({<OPERATION_NAME*={'PRODUCTION'}>}(sum(USED_HOURS)/sum(total(AVAILABLE_HOURS))))

MAINTENANCE % =sum({<OPERATION_NAME*={'MAINTENANCE'}>}(sum(USED_HOURS)/sum(total(AVAILABLE_HOURS))))

IDLE % = 1 - sum(total USED_HOURS)/sum(total AVAILABLE_HOURS

AW:Value changes when a dimension is added

johnw — Wed, 23 Feb 2011 19:42:16 GMT

ENGINEERING %
=sum({<OPERATION_NAME*={'ENGINEERING'}>} USED_HOURS)
/sum(total AVAILABLE_HOURS)

PRODUCTION %
=sum({<OPERATION_NAME*={'PRODUCTION'}>} USED_HOURS)
/sum(total AVAILABLE_HOURS)

MAINTENANCE %
=sum({<OPERATION_NAME*={'MAINTENANCE'}>} USED_HOURS)
/sum(total AVAILABLE_HOURS)

IDLE % = 1 - sum(total USED_HOURS)/sum(total AVAILABLE_HOURS)

AW:Value changes when a dimension is added

Anonymous — Wed, 23 Feb 2011 21:01:50 GMT

Before using the set analysis my bar chart showed 63% stacked with Operation names , now the operation names display as separate bars, how do i make the stacked bars displaying idle 37% top of the 57 % ,5 % ,1%.. which should sum up to 100%...

basically i wanted all the operation names and idle as dimensions ...

AW:Value changes when a dimension is added

johnw — Wed, 23 Feb 2011 21:07:23 GMT

Remove operation as a dimension. Make sure your bar chart is set to stacked.

AW:Value changes when a dimension is added

Anonymous — Wed, 23 Feb 2011 22:06:12 GMT

Hi John

This works , thank you.

I noticed a weird thing , my tables have data from past 4-5 years , and i was actually working on one week of the data , i mean was validating with one week of data if the values matches with my qlikview report , everything matched when i selected data for 1 week , week is my dimension , when i cleared the selection , that means when no week is selected all the values are showing 92% idle ,

and for the graph where i did not use set analysis , the y - axis values shows 0.02,0.04,0.06,0.08 and so on(instead of 20%,40%,60%..) ....just dont understand whats happening ...i guess its doing an average for all the weeks....or something else....

Please Help....

AW:Value changes when a dimension is added

johnw — Wed, 23 Feb 2011 22:26:21 GMT

I don't know what you're saying the problem is. Guessing wildly, you may have set analysis that depends on a value being selected for week. I suspect that's a common mistake.

As for the other problem:

Properties -> Number -> checkmark "Show in Percent (%)"

AW:Value changes when a dimension is added

Anonymous — Wed, 23 Feb 2011 23:02:38 GMT

John ,

I guess i found where the problem is , as you said ...the available hours are specific to each week , so using total is summing up all the values and that value is used when calculating the util rate instead of available hours value for each week ,

here is the data when i selected for 2 weeks , 21504 = 10752 + 10752 , so thats why i have been getting the small values like 0.01 , 0.08 while calculating the Util rate for all the weeks , so total is not working for me here .

Can you please suggest any other option to calculate the Util rate based on each week Available Hours ?

For eg : A particular week has available hours as 10752 , and used hours is 6710 , 492,62 for the operations , Now the Util rate has to be calcuted as 6710/10752 ,492/10752 , 62/10752 so on.This gives me % for each week by its operation name.

Hope my question is clear ,

Here is what the data looks like when 2 weeks are selected,

Week	Operation	Sum (total(AVAILABLE_HOURS))	Sum (USED_HOURS)	Sum (USED_HOURS)/Sum (total(AVAILABLE_HOURS))
		21504.00	14040.50	65%
12/5/2010	ENGINEERING	21504.00	6710.12	31%
12/5/2010	MAINTENANCE	21504.00	492.08	2%
12/5/2010	PRODUCTION	21504.00	62.25	0%
12/5/2010	-	21504.00	0.00	0%
12/12/2010	ENGINEERING	21504.00	6131.52	29%
12/12/2010	MAINTENANCE	21504.00	559.27	3%
12/12/2010	PRODUCTION	21504.00	85.27	0%
12/12/2010	-	21504.00	0.00	0%