Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bbergstrom
Partner - Creator II
Partner - Creator II

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))

1 Solution

Accepted Solutions
kingsley101
Contributor III
Contributor III

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

View solution in original post

12 Replies
kingsley101
Contributor III
Contributor III

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

bbergstrom
Partner - Creator II
Partner - Creator II
Author

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

kingsley101
Contributor III
Contributor III

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

Thanks

bbergstrom
Partner - Creator II
Partner - Creator II
Author

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

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

bbergstrom
Partner - Creator II
Partner - Creator II
Author

While that worked the performance is about the same.

bbergstrom
Partner - Creator II
Partner - Creator II
Author

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

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

bbergstrom
Partner - Creator II
Partner - Creator II
Author

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)