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