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.

[luismadriz]

Please fix this one... just in case:

from

if (GetSelectedCount([Vacancy Code]=0)

to

if (GetSelectedCount([Vacancy Code])=0

[elie_issa]

I Tried your solution but still not working.

Note that when choosing a filter like year or month i am getting data.

it's all about performance. the expression is working fine.

Thanks again

[mato32188]

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

[martinpohl]

please check in a copy of your Chart that is not the getselectedcount-statement

edit your expression to case one and case two and check the respone time.

count distinct is not a performance function, even if the field is a linked field between tables.

maybe it is better to create a

1 as SumPeople

in your people-table and Change the Expression to sum (SumPeople) with your set expression

regards

[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

[elie_issa · Accepted solution]

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.