8 Replies Latest reply: Aug 11, 2017 2:06 PM by Saima SM RSS

    Count Responses

    Saima SM

      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

        • Re: Count Responses
          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

            • Re: Count Responses
              Saima SM

              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

                • Re: Count Responses
                  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

                    • Re: Count Responses
                      Saima SM

                      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
                          Kaushik Solanki

                          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

                            • Re: Count Responses
                              Saima SM

                              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.