topic Including an dimension in QlikView

Including an dimension

Anonymous — Wed, 26 Jan 2011 21:14:51 GMT

I have 2 expressions one for Availble Hours and one for used hours,

Idle Hours = Avaible Hours - Used Hours , i need to include Idle Hours in the hour-seg-prime dimension( In the Avaible Hours By Week graph) , i have attached my qlikview file ..

Please Help

Thanks..

Including an dimension

johnw — Wed, 26 Jan 2011 23:29:23 GMT

I don't know what you mean by including the idle hours in the dimension. Are you saying you want your dimension to look something like this?

HOUR_SEG_PRIME
Prime Time: 9 AM - 7 PM (450 idle)
PM-OS: 10 PM - 12 AM (3,210 idle)
...

Including an dimension

Anonymous — Wed, 26 Jan 2011 23:36:28 GMT

I Mean,

HOUR_SEG_PRIME:

Prime Time: 9 AM - 7 PM

PM-OS: 10 PM - 12 AM

Idle Hours

I want to show idle hours as one of the dimensions along with hour seg prime values..

Including an dimension

johnw — Thu, 27 Jan 2011 00:33:33 GMT

No idea if I'm understanding. Sounds like you only want USED hours broken down by HOUR_SEG_PRIME, and then for Idle hours to be separate. The total would still be available hours. Let's take the week of 12/19/2010. Your "Available Hours By Week" is currently a stacked bar for this information:

HOUR_SEG_PRIME Available
Prime Time:9 AM - 7 PM 4273.42
PM-OS:10 PM - 12 AM 822.70
PM-Eng:7 PM - 10 PM 1260.00
AM-OS:12 AM - 7 AM 2845.25
AM-Eng:7 AM - 9 AM 766.92

I think you want it to instead be a stacked bar for this information?

HOUR_SEG_PRIME Available
Prime Time:9 AM - 7 PM 2374.27
PM-OS:10 PM - 12 AM 384.45
PM-Eng:7 PM - 10 PM 534.05
AM-OS:12 AM - 7 AM 1313.93
AM-Eng:7 AM - 9 AM 268.63
Idle Hours 5092.95

It would probably be best to set this up in your data model. I have no way to reload your data model to test anything, but where your data looks like this:

USER_ID, HOUR_SEG_PRIME, AVAILABLE_HOURS, USED_HOURS
krathod, PM-Eng: 7 PM - 10 PM, 3.00, 0.63

I might instead do this:

User, Hour Seg Prime, Hours
krathod, PM-Eng: 7 PM - 10 PM, 0.63
krathod, Idle Hours, 2.37

But I don't know your data and I don't know what else you're doing with it, so I can't say whether or not that's a good idea for you. All I can say is that it would solve your problem IF what you wanted what a stacked bar with the numbers I showed above. There are likely other solutions.

Including an dimension

Anonymous — Thu, 27 Jan 2011 00:57:07 GMT

I am extremely sorry for the confusion ,

i have pointed you to wrong graph , actually its Used Hours by week graph NOT Available hours by week.

As you said if you consider week 12/19/2010

the dimension in the USED HOURS BY WEEK should appear something like below :

HOUR_SEG_PRIME Available
Prime Time:9 AM - 7 PM 4273.42
PM-OS:10 PM - 12 AM 822.70
PM-Eng:7 PM - 10 PM 1260.00
AM-OS:12 AM - 7 AM 2845.25
AM-Eng:7 AM - 9 AM 766.92

IdleHours 5093

Basically , i am calculating idle hours as sum(Avaible Hours) - sum(Used Hours).

Please refer to the selection in the attached qvw now , hope it makes some sense now....

Including an dimension

johnw — Thu, 27 Jan 2011 01:10:21 GMT

Doesn't matter which chart you modify if those are the numbers you want. If you were using my data model change:

Dimension 1 = Week
Dimension 2 = Hour Seg Prime
Expression = sum(Hours)

Clean charts like this are why I tend to prefer data model changes to chart charges. If you don't want to change the data model, and I assume you don't, you could probably do this:

Dimension = Week
Expression 1 = sum({<HOUR_SEG_PRIME*={'Prime Time: 9 AM - 7 PM'}>} USED_HOURS)
Expression 2 = sum({<HOUR_SEG_PRIME*={'PM-OS: 10 PM - 12 AM'}>} USED_HOURS)
Expression 3 = sum({<HOUR_SEG_PRIME*={'PM-Eng: 7 PM - 10 PM'}>} USED_HOURS)
Expression 4 = sum({<HOUR_SEG_PRIME*={'AM-OS: 12 AM - 7 AM'}>} USED_HOURS)
Expression 5 = sum({<HOUR_SEG_PRIME*={'AM-Eng: 7 AM - 9 AM'}>} USED_HOURS)
Expression 5 = count(DISTINCT TESTER)*7*AVAILABLE_HOURS - USED_HOURS

That only works if you can't add new dimension values, or remember to add expressions every time you add new dimension values.

Including an dimension

Anonymous — Thu, 27 Jan 2011 01:51:41 GMT

This does not work as per my requiremment the reason why i am not prefering to change data model is i have some complex sql query calculation in my datamodel , i am joining 11 tables on different keys , its so confusing , the case statement what i have for available hours in my sql query is :

CASE
WHEN B.HOUR_SEG_PRIME = 'AM-OS:12 AM - 7 AM' THEN 0.292
WHEN B.HOUR_SEG_PRIME = 'AM-Eng:7 AM - 9 AM' THEN 0.083
WHEN B.HOUR_SEG_PRIME = 'Prime Time:9 AM - 7 PM' THEN 0.417
WHEN B.HOUR_SEG_PRIME = 'PM-Eng:7 PM - 10 PM' THEN 0.125
WHEN B.HOUR_SEG_PRIME = 'PM-OS:10 PM - 12 AM' THEN 0.083
END
)* 24 AS Available_Hours

So i am taking this avaible hours and multiplying by 7 and multiplying with count of testers and using it as an expression for getting available hours for the testers selected for a particualr category for the selected week.

and used_hours is an already calculated column in my table it is prettry staright forward that sum(used_hours) is the total usage by selected testers.

all i wanted is the value obtained by the expression

"(Count (DISTINCT TESTER)*7*AVAILABLE_HOURS) - sum(used_hours)" to be displayed as one of the dimensions below the HOUR_SEG_PRIME values.

Including an dimension

johnw — Thu, 27 Jan 2011 02:40:54 GMT

Debugged. Had an extra space in the HOUR_SEG_PRIME values and needed to aggregate the Available and Used hours a little differently to calculate the idle hours. Also put expressions in opposite order:

Dimension = Week
Expression 1 = sum(aggr(count(DISTINCT TESTER)*7*AVAILABLE_HOURS,WEEK,HOUR_SEG_PRIME))-sum(USED_HOURS)
Expression 2 = sum({<HOUR_SEG_PRIME*={'AM-Eng:7 AM - 9 AM'}>} USED_HOURS)
Expression 3 = sum({<HOUR_SEG_PRIME*={'AM-OS:12 AM - 7 AM'}>} USED_HOURS)
etc.

See attached. Is that what you wanted?

Including an dimension

Anonymous — Thu, 27 Jan 2011 04:32:36 GMT

Thats exactly what i wanted ..thank you very much!