5 Replies Latest reply: Mar 9, 2017 3:40 AM by Michael Angelo Arocena RSS

    Aggr the if else

    Michael Angelo Arocena

      Hello Qlik Community

       

      I would like to ask if this is possible to qlik sense,

      So, I have a IF Statement where if Field1 is equal to 100%, then 1 else 0.

          

      IDDateField 1If Statement
      0011/1/201767%0
      0021/10/201775%0
      0031/11/2017100%1
      0041/12/2017100%1
      0051/25/2017100%1
      0012/1/2017100%1
      0022/3/201785%0
      0032/16/2017100%1
      0042/21/2017100%1
      0052/25/2017100%1

       

      Now, how can I sum these all 1s and show it thru KPI chart.

       

      JAN = 3

      FEB = 4

       

      any ideas ?

        • Re: Aggr the if else
          Andrey Khoronenko

          Hi Michael,

           

          Script code

           

          Directory;

          LOAD ID,

              Date,

              [Field 1],

              Month(Date) as Month, //add field!

              [If Statement]

          FROM

          [Path your table];


          On sheet create new table/

          Dimension: Month

          Expression: Count ({<[Field 1]={'100%'}>}ID)


          Regards,

          Andrey

            • Re: Aggr the if else
              Michael Angelo Arocena

              Thank you, Andrey for the quick response.

              But I just found out that I have another scenario.

                     

                     

              IDDateNumeratorDenominatorField 1If Statement
              11/1/201730045067%0
              21/10/201745060075%0
              31/11/2017500500100%1
              41/12/2017600600100%1
              51/12/2017560560100%1
              61/25/2017250250100%1
              71/25/201731035088%0
              12/1/2017380380100%1
              22/3/201759570085%0
              32/3/2017500500100%1
              42/16/2017550550100%1
              52/21/2017400400100%1
              62/25/2017220220100%1

              Field 1 = Numerator / Denominator

               

              In the table above, we have 2 records for 1/12/2017, since both records reach 100%, we count the date as 1, even with different IDs (we are not counting the ID). For 1/25/2017, since 1 record did not attain 100% (ID #7) , we cannot consider it to add even ID #6 attain 100%.

               

              We are counting days, days that every record reaches 100%

               

              Now, how can I sum these all 1s and show it thru KPI chart.

               

              JAN = 2 (because only 1/11/2017 and 1/12/2017 reaches 100%)

              FEB = 4 (because only 2/1/2017, 2/16/2017, 2/21/2017, 2/25/2017)

               

               

              Regards,

               

              mike

                • Re: Aggr the if else
                  Jonathan Dienst

                  Assuming you have a Month field as well as Date Field, this should work:

                   

                       Dimension: Month

                       Expression: =Sum(Aggr(If(Min([Field 1]) = 1, 1), Date, Month))

                   

                  (Assumes that Field 1 has values 0 - 1 formatted as %)

                  • Re: Aggr the if else
                    Prashant Sangle

                    Hi,

                     

                    You can try like this in script.

                    Test:

                    LOAD Id,

                         Date,

                         Numer,

                         Denom

                    FROM

                    Book1.xlsx

                    (ooxml, embedded labels, table is Sheet1);

                     

                     

                    Left Join

                    Load Date,Sum(Numer)/Sum(Denom) as %age

                    Resident Test

                    group by Date;

                     

                    then in chart take expression like

                    count(DISTINCT if(%age=1,Date))

                     

                    Regards,

                    Prashant Sangle

                    • Re: Aggr the if else
                      Michael Angelo Arocena

                      Thank you guys , really appreciate your help.

                      One last thing and I think who can answer this will be the correct answer.

                      so I have a table

                       

                           

                      ZoneDateNumeratorDenominatorcalculation(not part of the table, this calculation will be applied inside set analysis)
                      A2/10/2017172085%
                      A2/11/20172020100%
                      A2/12/20172020100%
                      A2/13/20172020100%
                      A2/14/20172020100%
                      A

                      3/1/2017    

                      152075%
                      A3/2/2017162080%
                      A3/3/20172020100%
                      A3/4/20172020100%
                      A3/5/20172020100%
                      B2/10/2017192095%
                      B2/11/20172020100%
                      B2/12/20172020100%
                      B2/13/20172020100%
                      B2/14/20172020100%
                      B3/1/2017     2020100%
                      B3/2/20172020100%
                      B3/3/20172020100%
                      B3/4/20172020100%
                      B3/5/20172020100%

                       

                       

                      I want to count the dates which it hit 100% based on the calculation (Numerator/Denominator). Here comes the challenge, I will have a "Month" filter. For this scenario, the user click "March 2017", that means it previous month is "February 2017". So everytime the user filter the months, the "Month -1" always previous of "Month". Expected Output is.

                       

                                       Month - 1         Month

                      Zone A =         4                   3

                       

                      Zone B =         4                   5

                       

                      Sorry, I'm still learning Qlik. But thank you guys! 

                       

                      Regards,

                       

                      Mike