6 Replies Latest reply: Dec 22, 2017 1:46 AM by Elie Issa RSS

    GetSelectedCount Function in Qlik Sense performance issue

    Elie Issa

      Dear All,

       

      Please i need assistance in the below expression when using GetSelectedCount function in Qlik Sense.

       

      It's taking too much time to give result in case no filter (check attachment)

       

       

      if (GetSelectedCount([Vacancy Code]=0) ,

       

       

      count ({<

      [Hired Flag] = {'Yes'},

      [Vacancy SDate] = {"$(= '>=' & date($(vBOMFilterDate)) & '<=' & date($(vEOMFilterDate)))"}

       

       

      >}

      distinct [Person Id]),

       

       

      count ({<

      [Hired Flag] = {'Yes'},

      [Vacancy SDate] = {"$(= '>=' & date($(vVacancyFilterDate)) & '<=' & date($(vEOMFilterDate)))"}

       

       

      >}

      distinct [Person Id]))

       

       

       

      Thanks for your support.

        • Re: GetSelectedCount Function in Qlik Sense performance issue
          Luis Madriz

          Please fix this one... just in case:

          from

          if (GetSelectedCount([Vacancy Code]=0)

          to

          if (GetSelectedCount([Vacancy Code])=0

          • Re: GetSelectedCount Function in Qlik Sense performance issue
            Martin Baculik

            Hi Elie,

             

            I would suggest to use boolean instead of Yes,No.

            1) Create a flag field in Script Load Editor  like if([Hired Flag]='Yes',1,0) as @HiredFlag and use it in your set analysis

            2) I would format your variable as date before using it in set analysis like vVacancyFilterDate = date(XXX)

            3) check if this works for you (you can remove date function if you have previously formated date variables)


            count([Vacancy SDate] = {"$ (>= date($(vVacancyFilterDate)) <= date($(vEOMFilterDate)))"} distinct PersonID)

            count([Vacancy SDate] = {"$ (>= date($(vBOMFilterDate)) <= date($(vEOMFilterDate)))"} distinct PersonID)


            PS: if your PersonID is alphanumeric, you can use autonumber function to get integers instead, which would help to count distinct persons faster.


            Thanks.


            BR

            Martin



            • Re: GetSelectedCount Function in Qlik Sense performance issue
              Marcus Sommer

              I think that avoiding the if-loop would increase the performance most. Further measures are using a numeric flag for [Hired Flag] and avoiding the date-formatting like hinted from Martin. Also I suggest to use a slightly different writing of the >= <= comparing. This meant your expression might be looking like:

               

              count ({< [Hired Flag] = {1},

                   [Vacancy SDate] = {">=$(=pick(rangemax(ceil(1 / getselectedcount([Vacancy Code])), 0) + 1,

                        $(vBOMFilterDate), $(vVacancyFilterDate)))<=$(vEOMFilterDate)"}

                >} distinct [Person Id])

               

              whereby the right date-variable will be returned from the pick() - expression and date() is directly left because of the >= <= comparing which takes the numeric part of a value and not the string-interpretation. To see how the logic worked just put the pick-expression into a textbox.

               

              If this is yet too slow than are any issues with the datamodel.

               

              - Marcus

              • Re: GetSelectedCount Function in Qlik Sense performance issue
                Elie Issa

                Thank you all for your support but the problem seems was from adding the Master Calendar Date in the table. when removing the master calendar date from the table the expression is working fine.

                 

                Thanks again.