Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a set of employees and data on the projects they are working on.
In a pivot table I use the following formula to work out the percentage they are billable:
=Sum({$<[Time category group] = {"Productive"}>+ <[Time category group] = {"Billable"}>} [Project time])/sum([Project time])
I then use the visual clues facility to highlight cases above 90% as Green, 80-90% - amber and the rest are Red.
In addition to the pivot table I want to create a count of employees that are Red, Amber and Green.
I can create the ‘colour’ as a first expression in a new object:
= if(Sum({$<[Time category group] = {"Productive"}>+ <[Time category group] = {"Billable"}>} [Project time])/sum([Project time]) < 0.8, 'Red',if(Sum({$<[Time category group] = {"Productive"}>+ <[Time category group] = {"Billable"}>} [Project time])/sum([Project time])>= 0.9,'Green','Amber' ))
I then tried counting the results of the first column:
=if( Column(1) = 'Amber', count(DISTINCT [Name of employee]) )
This returns a value of one. I have tried numerous other permutations with count and agg and sum, but I do not seem to be able to obtain a count.
Does anyone have any ideas?
Regards
Jason
Could you post a sample of your qvw file? Or at least an image of the data model?
Jason,
I am not able to pass on the data for obvious reasons, but the model is just one table as all the data is on an excel spreadsheet.
The data is hundreds of lines of projects, with individuals having multiple entries (i.e. multiple projects, holidays, sickness....) which I aggregate in Qlikview.
I have tried to attach an image of the data model, but it did not work so here are the headings:
Amount, Area, Ass emp.,BK,Charged on, Cost, Cost Ctr, Crcy, Created On, Date, Description,End date, FTE,Link,Name of employee or applicant,Period, Pers. No.,PK, Planned hours, Prctr.,Prof. Ctr., Projdef, Project Number, Project time, Project time1, Remark, Resource, Start pro.,Status,T&D trv time,Task,Time Category, Time Category Group, Typ, Eage Type Long Text, WTE
Regards
Jason
I'm sorry mate but you're going to have to explain your data a bit more as looking at your expressions it seems tad confusing! For example, in your set analysis expression:
=Sum({$<[Time category group] = {"Productive"}>+ <[Time category group] = {"Billable"}>} [Project time])/sum([Project time])
did you really mean to use the '+' symbol in there? In set analysis this performs a union between two sets of data which I don't think you want. More likely (I think) is:
=Sum({$<[Time category group] = {Productive,Billable}>} [Project time])/sum([Project time]
(you had an extra parenthesis at the end there)
But then that confuses me a bit as it seems that to figure out what to bill you need to look at multiple records to make up the criteria. Or something. Are you saying here that for every record where the Time Category Group is "Productive" or "Billable" you need to divide the project time of that record by the total project time for all such records?
If so I don't think the above will work (although can't be sure without testing). I think you need to use TOTAL:
=Sum({$<[Time category group] = {Productive,Billable}>} [Project time])/
Sum({$<[Time category group] = {Productive,Billable}>} TOTAL <[Name of employee]> [Project time])
Can you not scramble the names and randomise the figures and post a sample?
On a wider note when presented with mixed dimensional and fact data like this I would always favour cleaning up the structure in the script first. For example, load just distinct dimensional (employee) details into one table, giving the records an Employee_ID by using (maybe) AutoNumber([Employee Name]).
Then, load all the fact detail into a separate table, again using AutoNumber() to create Employee_ID to link on. This approach gives a cleaner model to work with and enables you to optimise the calculations more e.g. in your Employee table add a field called Counter_Employee with a value of 1 for every record. Then, instead of COUNT(DISTINCT [Name of Employee]) you can simply use SUM(Counter_Employee) which will calculate a lot faster. Probably not an issue over just a few hundred rows but good practice all the same.
Any calculations that don't depend on selections, move into the script. Easier to work with and faster.
Hope this help - without seeing the data I can't really help much more, sorry.
Jason
Jason,
Thanks for your response.
The data is very similar to the following (there are more description columns and categories, but is essentially like this):
Name of employee or applicant | Project number | Time category group | Project time |
Jason | 001 | Holiday | 7.5 |
Jason | 901 | Billable | 35 |
Fred | 904 | Billable | 50 |
Jason | 803 | Productive | 15 |
Jason | 002 | Public Holiday | 7.5 |
Fred | 002 | Public Holiday | 7.5 |
Jason | 009 | Non-Billable | 22.5 |
Fred | 909 | Billable | 22.5 |
So – Productive /Billable percentage is :
Jason – 57.1% Fred – 90.6%
I tried out you suggestion for the billable/productivity calculation and it gave me the same answers as my calculation - so I got there, but not with the easist to read statement!
I tried the statement with Total and that correctly gives me the total billable/productivity hours. Unfortunately it still does not enable me to count the number of employees in Red, Amber and Green.
Your suggestions on scripting procedures were very interesting and I will go away and investigate them.
Thanks very much for your help
Jason
Hi Jason,
Try this - seems to work.
Jason
Hey Jason,
Superb- just what I needed. I spent over a day trying to work that out, so I really appreciate your help.
Thanks
Jason
No worries - I'm sure there's a better way but that'll work for now!
Can U please explain how is this expression working?
=
COUNT({<Employee={"=SUM({<TimeCategory={Productive,Billable}>} ProjectTime)/SUM(ProjectTime)<0.8"} >} DISTINCT Employee)
you given, Employee={"=SUM({<TimeCategory={Productive,Billable}>} ProjectTime)/SUM(ProjectTime)<0.8"} >} I am not able to understand this.
Apparently nested aggregation is not allowed in QlikView.
This calculates each employee’s billable percentage separately (the equals sign at the front of a calculation signifies that it should be calculated at a document level) and then counts the number of employees who are less than 50% billable.
Hope this helps
Jason