Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
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))

12 Replies
kingsley101
Contributor III
Contributor III

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

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
Partner - Creator II
Partner - Creator II
Author

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

Thanks!