17 Replies Latest reply: Jul 20, 2016 8:54 AM by Joanna Seldon RSS

    count if , and distinct times after target is met

    Joanna Seldon

      Hi

       

      in regards to temperatures 37.5

       

      I wish to count only, after a certain number temperature target has been reached '37.5'. and only the distinct times temperature dips below 37.5

       

      please help

       

      test 1

       

      TestNo     Temp

      1               37.5

      2               37.5

      3               37.5        = 0 dips

       

      Test 2

       

      TestNo     Temp

       

      1               36.5

      2               37.5    (Target temp has been reached)

      3               37.5

      4               36.5    (dip has occurred)

      5               36.5

      6               37.5 

      7               37.5

      8               37.5   =    1 dip

       

      please help

        • Re: count if , and distinct times after target is met
          Alluraiah Allu

          Hi

          It is like this ?

           

          I have attached a straight table with some conditional colors .

           

           

          Edited : Attachment with pivot :  working fine

            • Re: count if , and distinct times after target is met
              Joanna Seldon

              Hiya

               

              the pivot is exactly right, but I am using qlik sense. I need to add this calculation to a bar chart. how many dips on a date

               

              please help

                • Re: count if , and distinct times after target is met
                  Alluraiah Allu

                  Hi

                   

                  Please find the qvw as per requirement in bar chart.

                    • Re: count if , and distinct times after target is met
                      Joanna Seldon

                      Hi

                       

                      sorry for delay in reply...

                       

                       

                      IF(Previous(Temp)-Temp<'0','Dip','Normal') as  Temp_variation

                       

                       

                      the first issue i have is data is being loaded from a sql database ..

                       

                      I have added this as an if statement, it seems to only work if you place >'0' to a <'0'

                       

                      the issue I am having is when it sees the same value ,

                       

                      i keep getting this for an room id

                       

                      RoomID is in same table..

                       

                      RoomID    TestNo     Temp   Temp_Variation                 should be

                      1                1              37.5        NORMAL                       

                      1                2              34.8        NORMAL                        DIP

                      1                3              37.5        NORMAL

                      1                4              37.5        NORMAL

                      1                5              37           NORMAL

                      1                6              37           NORMAL

                      1                7              37.5        NORMAL

                      1                8              37.5        DIP

                       

                       

                      IF(Previous(Temp)-Temp>'0','Dip','Normal') as  Temp_variation

                       

                      IF I place the > in the correct way as in your script I get

                       

                      RoomID    TestNo     Temp   Temp_Variation                 should be

                      1                1              37.5        NORMAL                       

                      1                2              34.8        NORMAL                        DIP

                      1                3              37.5        NORMAL

                      1                4              37.5        NORMAL

                      1                5              37           NORMAL

                      1                6              37           NORMAL

                      1                7              37.5        NORMAL

                      1                8              37.5        NORMAL

                       

                      Please help

                        • Re: count if , and distinct times after target is met
                          Alluraiah Allu

                          Hi

                           

                          IF(Previous(Temp)-Temp>0,'dip','Normal') as  Temp_variation

                           

                           

                           

                          this gives  the value if temperature in the series decreased

                           

                          eg:

                          37.5

                          37.5

                          36.5

                          36.5

                          ..

                          ...

                          ...

                          ...

                           

                          here it will count as one 'dip'

                           

                          37.5

                          37.5

                          36.5

                          36.5

                          36.5

                          36.5

                          35.5

                          ..

                          ...

                           

                          ..

                           

                          here it will count as two dips

                           

                           

                           

                          Note :  in the properties of PIVOT TABLE

                           

                          sort :

                           

                          Testno: LOAD ORDER   original

                           

                          Temp : Numeric value


                          emp_Variation : state :Asecnding





                            • Re: count if , and distinct times after target is met
                              Joanna Seldon

                              Hi

                               

                              I have tried to use your script and amend it and still no luck. please see below

                               

                              SOURCE:

                              LOAD "RoomID",

                                "TestNo",

                                 "Temp";

                              SQL SELECT "RoomID",

                                "TestNo",

                                 "Temp"
                              FROM dbo.RoomTemp;

                              NoConcatenate
                              TEMP1:
                              LOAD *,
                              IF(Previous(Temp)-Temp<0,'dip','Normal') as  Temp_variation
                              Resident SOURCE Order by TestNo asc,  Temp asc  , "RoomID" asc ;
                              drop table SOURCE;

                               

                              for temp variation am I supposed to sort by expression on the table?

                               

                              please help

                                • Re: count if , and distinct times after target is met
                                  Alluraiah Allu

                                  Hi

                                   

                                  please don't do any sorting in  EDITOR :

                                   

                                  SOURCE:

                                  LOAD "RoomID",

                                    "TestNo",

                                    "Temp";

                                  SQL SELECT "RoomID",

                                    "TestNo",

                                    "Temp"
                                  FROM dbo.RoomTemp;

                                  NoConcatenate
                                  TEMP1:
                                  LOAD *,
                                  IF(Previous(Temp)-Temp<0,'dip','Normal') as  Temp_variation
                                  Resident SOURCE  ;
                                  drop table SOURCE;

                                   

                                   

                                   

                                   

                                     in the properties of PIVOT TABLE..............>Sort................>

                                   

                                   

                                   

                                  Testno: LOAD ORDER   original

                                   

                                  Temp : Numeric value

                                   

                                   

                                  emp_Variation : state :Asecnding

                                    • Re: count if , and distinct times after target is met
                                      Joanna Seldon

                                      Hi

                                       

                                      I have tried the above it did not work

                                      here is the result:

                                      RoomID    TestNo     Temp   Temp_Variation                 should be

                                      1                1              37.5        Dip                                  NORMAL     

                                      1                2              34.8        Dip                                  DIP

                                      1                3              37.5        NORMAL                        NORMAL

                                      1                4              37.5        NORMAL                        NORMAL

                                      1                5              37           NORMAL                        NORMAL

                                      1                6              37           NORMAL                        NORMAL

                                      1                7              37.5        NORMAL                        NORMAL

                                      1                8              37.5        DIP                                  NORMAL

                                       

                                      I am using qlik sense..and a straight table. I will then be using a Kpi to count the number of dips.

                                       

                                      sorry

                                       

                                      please help

                                       

                                      I did try putting the

                                        • Re: count if , and distinct times after target is met
                                          Alluraiah Allu

                                          Hi

                                           

                                          please post your data (sample date) at least .

                                           

                                          I am understand ,why it is not working for you  .

                                           

                                          FYI

                                          I have done this in QLIK sense as well .

                                           

                                           

                                           

                                          .please check my QVF.

                                            • Re: count if , and distinct times after target is met
                                              Joanna Seldon

                                              Hi

                                               

                                              I don't know if you can help

                                               

                                              I am now using your formulary expression

                                               

                                              IF(Previous(Temp)-Temp>0,'dip','Normal') as  Temp_variation

                                               

                                              this works great...I got another  issue  please ...how can I amend the above to only show a normal. if temp is 35 and above?

                                               

                                              as this record

                                               

                                              RoomID    TestNo     Temp   Temp_Variation              Hope to be

                                              1                1              37        NORMAL                        NORMAL     

                                              1                2              36        DIP                                 NORMAL

                                              1                3              35        DIP                                 NORMAL

                                              1                4              36        NORMAL                        NORMAL

                                              1                5              35        DIP                                 NORMAL

                                              1                6              35        NORMAL                        NORMAL

                                              1                7              35        NORMAL                        NORMAL

                                               

                                              please help