Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count Responses

Dear All,

I have data for employees. (sample data is attached in excel file)    

   

EMPIDClass1AttClass2AttClass3AttClass4Att
278991111
278551111
278731000

I need to calculate followings.

1- No. of Emp who attended AT LEAST 1 of 4 Classes

2- No. of Emp who attended AT LEAST 3 of 4 Classes

3- No. of Emp who attended 1+ Classes (More than 1 class)

4- No. of Emp who attended 2+ classes ( more than 2 classes)

5- No. of Emp who attended 3+ classes ( more than 3 classes)

6- No. of Emp who attended  All Classes

7- 5- No. of Emp who didn't  attend any Class

Much appreciated if someone could help. Thanks

1 Solution

Accepted Solutions
sunny_talwar

Try this

1) =Count({<EMPID = {"=RangeSum(Class1Att, Class2Att, Class3Att, Class4Att) >= 3"}>} DISTINCT EMPID)

2) =Count({<EMPID = {"=RangeSum(Class1Att, Class2Att, Class3Att, Class4Att) >= 2"}>} DISTINCT EMPID)

3) =Count({<EMPID = {"=RangeSum(Class1Att, Class2Att, Class3Att, Class4Att) >= 1"}>} DISTINCT EMPID)


Capture.PNG

View solution in original post

8 Replies
sunny_talwar

Number 3 and 4 seems to be looking for the same number, isn't it?

I have used a crosstable load to transform your data and then this

1) =Count({<EMPID = {"=Sum(Value) >= 3"}>} DISTINCT EMPID)

2) =Count({<EMPID = {"=Sum(Value) >= 2"}>} DISTINCT EMPID)

3) =Count({<EMPID = {"=Sum(Value) >= 1"}>} DISTINCT EMPID)

Capture.PNG

Not applicable
Author

Thanks,

Is there any other way, because I don't want to use cross tab as I have lots of other fields in this table to be used in dashboards. I work in qlik sense. thanks

sunny_talwar

Try this

1) =Count({<EMPID = {"=RangeSum(Class1Att, Class2Att, Class3Att, Class4Att) >= 3"}>} DISTINCT EMPID)

2) =Count({<EMPID = {"=RangeSum(Class1Att, Class2Att, Class3Att, Class4Att) >= 2"}>} DISTINCT EMPID)

3) =Count({<EMPID = {"=RangeSum(Class1Att, Class2Att, Class3Att, Class4Att) >= 1"}>} DISTINCT EMPID)


Capture.PNG

Not applicable
Author

Thanks a lot.  I am not sure you provided for AT LEAST 1, 2, 3 OR  for More Than 1, 2, 3. I appreciate if you could please provide ne formula under each option thanks

1- No. of Emp who attended AT LEAST 1 of 4 Classes

2- No. of Emp who attended AT LEAST 3 of 4 Classes

3- No. of Emp who attended 1+ Classes (More than 1 class)

4- No. of Emp who attended 2+ classes ( more than 2 classes)

5- No. of Emp who attended 3+ classes ( more than 3 classes)

6- No. of Emp who attended  All Classes

7- No. of Emp who didn't  attend any Class

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

See below.

1. =Count({<EMPID = {"=RangeSum(Class1Att, Class2Att, Class3Att, Class4Att) >= 1"}>} DISTINCT EMPID)

2. =Count({<EMPID = {"=RangeSum(Class1Att, Class2Att, Class3Att, Class4Att) >= 3"}>} DISTINCT EMPID)

3. =Count({<EMPID = {"=RangeSum(Class1Att, Class2Att, Class3Att, Class4Att) >= 1"}>} DISTINCT EMPID)

4. =Count({<EMPID = {"=RangeSum(Class1Att, Class2Att, Class3Att, Class4Att) >= 2"}>} DISTINCT EMPID)

5. =Count({<EMPID = {"=RangeSum(Class1Att, Class2Att, Class3Att, Class4Att) >= 3"}>} DISTINCT EMPID)

6. =Count({<EMPID = {"=RangeSum(Class1Att, Class2Att, Class3Att, Class4Att) =4"}>} DISTINCT EMPID)

7. =Count({<EMPID = {"=RangeSum(Class1Att, Class2Att, Class3Att, Class4Att) = 0"}>} DISTINCT EMPID)

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

Thanks Kaushik,

I didn't find difference between

2- No. of Emp who attended AT LEAST 3 of 4 Classes

Count({<EMPID = {"=RangeSum(Class1Att, Class2Att, Class3Att, Class4Att) >= 3"}>} DISTINCT EMPID)

5-No. of Emp who attended 3+ classes ( more than 3 classes)

5. =Count({<EMPID = {"=RangeSum(Class1Att, Class2Att, Class3Att, Class4Att) >= 3"}>} DISTINCT EMPID)

similarly for point 1 and 3. Here is different b/w AT LEAST and  MORE THAN. Can you please help more in this regard thanks.

sunny_talwar

At least 3 of 4 means, you want either 3 1s or 4 1s

3+ means, you want either 3 1s or 4 1s....

What is the difference between them?

Not applicable
Author

3+ means more than 3

1+ means more than 2 not including 1 1s. But you are also right here if we include 3 1s in 3+ as well.

Any how all the formulas are working. Thanks for the help.