Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Obtaining a count of types from an expression

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

1 Solution

Accepted Solutions
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Hi Jason,

Try this - seems to work.

Jason

View solution in original post

10 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Could you post a sample of your qvw file?  Or at least an image of the data model?

Not applicable
Author

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

Jason_Michaelides
Luminary Alumni
Luminary Alumni

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

Not applicable
Author

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

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Hi Jason,

Try this - seems to work.

Jason

Not applicable
Author

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

Jason_Michaelides
Luminary Alumni
Luminary Alumni

No worries - I'm sure there's a better way but that'll work for now!

Not applicable
Author

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.

Not applicable
Author

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