Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

elie_issa
Contributor II

GetSelectedCount Function in Qlik Sense performance issue

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.

1 Solution

Accepted Solutions
elie_issa
Contributor II

Re: GetSelectedCount Function in Qlik Sense performance issue

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.

6 Replies
luismadriz
Valued Contributor

Re: GetSelectedCount Function in Qlik Sense performance issue

Please fix this one... just in case:

from

if (GetSelectedCount([Vacancy Code]=0)

to

if (GetSelectedCount([Vacancy Code])=0

elie_issa
Contributor II

Re: GetSelectedCount Function in Qlik Sense performance issue

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
Contributor III

Re: GetSelectedCount Function in Qlik Sense performance issue

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
Valued Contributor II

Re: GetSelectedCount Function in Qlik Sense performance issue

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

Re: GetSelectedCount Function in Qlik Sense performance issue

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
Contributor II

Re: GetSelectedCount Function in Qlik Sense performance issue

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.

Community Browser