Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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
MVP
MVP

Re: Count Responses

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

8 Replies
MVP
MVP

Re: Count Responses

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

Re: Count Responses

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

MVP
MVP

Re: Count Responses

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

Re: Count Responses

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

Re: Count Responses

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

Not applicable

Re: Count Responses

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.

MVP
MVP

Re: Count Responses

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

Re: Count Responses

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.