10 Replies Latest reply: Jul 28, 2013 2:22 PM by Amit Gupta RSS

    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

        • Obtaining a count of types from an expression
          Jason Michaelides

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

            • Obtaining a count of types from an expression

              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

               

                • Obtaining a count of types from an expression
                  Jason Michaelides

                  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