topic Default Values Between Two Dates in App Development

Default Values Between Two Dates

bbergstrom — Tue, 23 Oct 2018 17:43:58 GMT

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

Re: Default Values Between Two Dates

kingsley101 — Tue, 23 Oct 2018 19:45:08 GMT

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

Re: Default Values Between Two Dates

bbergstrom — Tue, 23 Oct 2018 20:51:28 GMT

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

Re: Default Values Between Two Dates

kingsley101 — Wed, 24 Oct 2018 06:56:22 GMT

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

Thanks

Re: Default Values Between Two Dates

bbergstrom — Wed, 24 Oct 2018 13:36:30 GMT

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

Re: Default Values Between Two Dates

kingsley101 — Wed, 24 Oct 2018 13:56:49 GMT

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

Re: Default Values Between Two Dates

bbergstrom — Wed, 24 Oct 2018 14:15:53 GMT

While that worked the performance is about the same.

Re: Default Values Between Two Dates

bbergstrom — Wed, 24 Oct 2018 14:23:57 GMT

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?

Re: Default Values Between Two Dates

kingsley101 — Wed, 24 Oct 2018 14:31:55 GMT

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

Re: Default Values Between Two Dates

bbergstrom — Wed, 24 Oct 2018 15:05:39 GMT

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)

Re: Default Values Between Two Dates

kingsley101 — Thu, 25 Oct 2018 06:34:06 GMT

Hi Brian.

Assuming that is the condition you used within the expression and not the entire expression itself:

Please see my attached qvf. It works perfectly for me. Is there a chance the user has selected another field? (eg. if you select a customer, due to association, you will only see invoice numbers for that customer, and because that customer has only been invoiced once, 1 isn't equal to the total of 10 invoices numbers -IF(Count(Invoice#)=Count({<Invoice#=>}Invoice#,'No selections have been made','Selections have been made)

)

The code works, it's likely that something is selected without you knowing.

Hope this helps.

Re: Default Values Between Two Dates

tomhoch2 — Thu, 25 Oct 2018 11:44:18 GMT

Hi Brian,

If you are trying to gain efficiency, it is better to convert the if statements to set analysis. I am seeing two types of set equations, one if the user has not made date selections and one if the user has made date selections. So the below formula will only check once if the the user has made a selection and then provide the appropriate set analysis, either the date range for the default selections (variables) or if date range is selected the FETCCount, no reason in the second set analysis to use TranDateTime because the user has already selected it.

Count({<$(=if((GetSelectedCount(BillingMonthNameAbbr)+GetSelectedCount([Transaction Date])+GetSelectedCount([Transaction Month])+GetSelectedCount([Transaction Week]))=0,'TranDateTime={">='&date(vExceptFrom)&'<='&date(vExceptTo)&'"}','FETCCount={1}'))>} distinct TranDateTime)

Re: Default Values Between Two Dates

bbergstrom — Thu, 25 Oct 2018 13:30:10 GMT

I did have another filter applied. I'll modify the expression to ignore the other filters available and give it a whirl.

Thanks!