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]

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.

[tomhoch2]

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)

[bbergstrom]

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

Thanks!