Count If

[Scott_Data]

Hi all, 

New to QS, trying to figure this all out. I'm sure this is very basic. I'm trying to create 3 columns in a pivot table based off one column

I have a column 'Work Hours', that contains year to date data on the amount of time taken to complete a job. I then have an identifier 'Name' That I'm grouping by. I want a count of all jobs that have taken < 3hrs, >=3 & <=12hrs, >12hrs. 

can someone please point me in the right direction? Thank you. 

[RafaelBarrios · Accepted solution]

Hi @Scott_Data 

It would help if you paste some records so we can see better how is your data and what are you trying to do, but let’s give it a try.

the easiest way would be an IF for each option in different columns 

Column < 3 hrs
count(distinct if(sum(hours)<3,job_id))

column >= 3 and < 12
count(distinct if(sum(hours)>3 and sum(hours)<12,job_id))

and so on.

and a good trick would be to use ValueList, where you create your ranges as dimension and specify the formula for each dimension value.

https://community.qlik.com/t5/Design/ValueList-For-those-tricky-situations/ba-p/1476275

hope this works for you.

best,

Help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 🙂

[Scott_Data]

Hi @RafaelBarrios , 

I managed to figure it out eventually with the below. I'll look into the value list though. Thank you! 

COunt({$<[Work Hours]={"< 4"}>}[Work Hours])

COunt({$<[Work Hours]={">=4<=12"}>}[Work Hours])

COunt({$<[Work Hours]={"> 12"}>}[Work Hours])

[RafaelBarrios]

HI @Scott_Data 

Good to know that !!!

that was why i was asking for some records.

and its because if you have aggregated data like this

job,Work Hours
job1,25
job2,10
job3,12
job4,8

COunt({$<[Work Hours]={">=4<=12"}>}[Work Hours]) will do the job for you 👍

But if you have

job,date,Work Hours
job1,2022-01-01,3
job1,2022-01-02,13
job1,2022-01-05,9
job2,2022-01-01,5
job2,2022-01-03,5
job3,2022-02-01,6
job3,2022-02-04,8
job4,2022-03-01,8

Job1 will be considered in the 3 formulas

COunt({$<[Work Hours]={"< 4"}>}[Work Hours])
COunt({$<[Work Hours]={">=4<=12"}>}[Work Hours])
COunt({$<[Work Hours]={"> 12"}>}[Work Hours])

Best,