Default Values Between Two Dates

[bbergstrom]

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

[kingsley101 · Accepted solution]

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]

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

[kingsley101]

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

Thanks

[bbergstrom]

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]

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]

While that worked the performance is about the same.

[bbergstrom]

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]

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]

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)