Count Responses

[#1]

Dear All,

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

EMPID	Class1Att	Class2Att	Class3Att	Class4Att
27899	1	1	1	1
27855	1	1	1	1
27873	1	0	0	0

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

[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)

[#3]

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 · Accepted solution]

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)

[#5]

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]

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

[#7]

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?

[#9]

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.