Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Partner
Partner

Default Values Between Two Dates

I am working with a Qlik Sense sheet where the requirement is to default the data displayed to a predetermined time frame (last 30 days) unless the user selects data in one of the filters.  I have created two variables to store the from and to dates and I can get the expression below to work but the performance is terrible.  I am hoping someone has an alternative solution.  Below is the expression.

The first part of the expression evaluates if one of the filter has been selected.  If not then it looks for all of the values of TranDateTime between the two variables.  Otherwise it bases the dates on the filters.

=IF(ISNULL(GetFieldSelections(BillingMonthNameAbbr)) AND ISNULL(GetFieldSelections([Transaction Date])) AND ISNULL(GetFieldSelections([Transaction Month])) AND IsNull(GetFieldSelections([Transaction Week])),

IF(DATE(TranDateTime)>='$(vExceptFrom)' AND DATE(TranDateTime)<='$(vExceptTo)',TranDateTime),

IF([Fuel Exception Type]='Exceeds Non-Fuel Limit',TranDateTime))

Tags (2)
1 Solution

Accepted Solutions
kingsley101
New Contributor III

Re: Default Values Between Two Dates

Hi Brian.

GetFieldSelections is quite expensive as far as I know. I would try count, to count the selected items and see if it's equal to the total count (the count without selections):

=IF(Count(BillingMonthNameAbbr)=Count({<BillingMonthNameAbbr=>} BillingMonthNameAbbr) AND ...//Continue this logic

Thanks

Kingsley

12 Replies
kingsley101
New Contributor III

Re: Default Values Between Two Dates

Hi Brian.

GetFieldSelections is quite expensive as far as I know. I would try count, to count the selected items and see if it's equal to the total count (the count without selections):

=IF(Count(BillingMonthNameAbbr)=Count({<BillingMonthNameAbbr=>} BillingMonthNameAbbr) AND ...//Continue this logic

Thanks

Kingsley

Partner
Partner

Re: Default Values Between Two Dates

So the count of the selections could be anywhere from 1 to all of the available values. 

kingsley101
New Contributor III

Re: Default Values Between Two Dates

Please can you try GetSelectedCount() instead and see if the performance improves.

Thanks

Partner
Partner

Re: Default Values Between Two Dates

It sort of works.  I tested it in a text box and it worked through the logic correctly.  When I modified my original expression in the table object the following is giving me an Invalid dimension error:

=IF(COUNT(BillingMonthNameAbbr)=COUNT({<BillingMonthNameAbbr=>}BillingMonthNameAbbr),

IF(DATE(TranDateTime)>='$(vExceptFrom)' AND DATE(TranDateTime)<='$(vExceptTo)',TranDateTime),

IF(FETCCount=1,TranDateTime))

kingsley101
New Contributor III

Re: Default Values Between Two Dates

Hi Brian.

Please try it by replacing GetFieldSelections() with GetSelectedCount(). I think it'll be more effecient but you will need to try on your model

=IF(GetSelectedCount(BillingMonthNameAbbr)=0 AND GetSelectedCount([Transaction Date])=0 AND GetSelectedCount([Transaction Month])=0 AND GetSelectedCount([Transaction Week])=0,

IF(DATE(TranDateTime)>='$(vExceptFrom)' AND DATE(TranDateTime)<='$(vExceptTo)',TranDateTime),

IF([Fuel Exception Type]='Exceeds Non-Fuel Limit',TranDateTime))

Thanks

Partner
Partner

Re: Default Values Between Two Dates

While that worked the performance is about the same.

Partner
Partner

Re: Default Values Between Two Dates

Would it make more sense to affect the variables?  In my case the vExceptFrom and vExceptTo variables are loaded via the loadscript (qvs).  Could I create a new variable where the default value is vExceptFrom and then change the variable to the values selected in the filter pane if any are selected?

kingsley101
New Contributor III

Re: Default Values Between Two Dates

Hi Brian.

Changing the variables won't make a noticeable advancement in the performance. It's most definitely the GetFieldSelections and GetSelectedCount functions are expensive. Please can you attach your qvf or qvw.

Thanks

Partner
Partner

Re: Default Values Between Two Dates

Sadly I cannot post the qvf as it would expose sensitive data.  Do you know why the following didn't work in the table object:

COUNT(BillingMonthNameAbbr)=COUNT({<BillingMonthNameAbbr=>}BillingMonthNameAbbr)