Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor III

Date filtering in set analysis for chart not working

Hi,

I'm trying to filter the data in my bar chart to only show the data with the date earlier than a date that is set by a user input variable.

I've tried using the following formula but it just gives me 0 for everything and does not show me the desired data. Is there something wrong with my set analysis expression for the date portion? 

sum({$<[Group]={'7401'},[Shop Date]={'<($(vSelectedOPMthDate))'}>}[Hr Clocked])

The "Group" portion of the set analysis is working fine, when I remove the [Shop Date]={'<($(vSelectedOPMthDate))'}  from the expression the plot appears again. It just does not work for the Shop Date portion.

Thank you!

Labels (2)
1 Solution

Accepted Solutions
Highlighted
New Contributor III

Re: Date filtering in set analysis for chart not working

Hi all,

Just wanted to update that I've finally solved it!!!!! So this was what was needed for the function to work. Read in another forum thread that we'd need another "$(=" for a function to be calculated. 

sum({$<[Group]={'7401'}, [Shop Date]={"<$(=$(vSelectedOPMthDate))"}>}[Hr Clocked])

Thank you for helping me to think through and troubleshoot this!

View solution in original post

17 Replies
Highlighted
Employee
Employee

Re: Date filtering in set analysis for chart not working

You'll need double quotes for < comparison ..

sum({$<[Group]={'7401'},[Shop Date]={"<($(vSelectedOPMthDate))"}>}[Hr Clocked])

Highlighted
New Contributor III

Re: Date filtering in set analysis for chart not working

Thank you for your advice!

I just tried it though and it still did not work. The plot completely disappeared even if I changed the single quote to double quotes.

Any other possibility of what I'm doing wrong?

Highlighted
Partner
Partner

Re: Date filtering in set analysis for chart not working

What's the definition of your variable vSelectedOPMthDate?

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Employee
Employee

Re: Date filtering in set analysis for chart not working

try this:
sum({$<[Group]={'7401'},[Shop Date]={"<$(vSelectedOPMthDate)"}>}[Hr Clocked])

Highlighted
New Contributor III

Re: Date filtering in set analysis for chart not working

(MonthEnd(AddMonths(today(2),$(vSelectedOutputMonth))))

 

where $(vSelectedOutputMonth) is either 0,1,2,3,etc as input by the user. Its a input variable that's supposed to represent number of months from this month.

 

When I display $(vSelectedOPMthDtae) it would show as 31/3/2020 if $(vSelectedOutputMonth) =0, and 30/4/2020, if $(vSelectedOutputMonth)=1, and so on.

Highlighted
New Contributor III

Re: Date filtering in set analysis for chart not working

Thanks Lisa_P,  but still did not work:(

 

Could it be due to my [Shop Date] being in numeric form when I display it, versus my $(vSelectedOPMthDate) being in DD/M/YYYY?

 

tried sum({$<[Group]={'7401'},[Shop Date]={"<date#($(vSelectedOPMthDate))"}>}[Hr Clocked]), but it did not work either.

Highlighted
Partner
Partner

Re: Date filtering in set analysis for chart not working

It could be that your vSelectedOutputMonth is intepreted as a double fraction 31/3/2020 = 0.00516.

Try adjusting your expression to the following (assuming DD/MM/YYYY is you default date format in the application).

 

=sum({$<
    [Group]={'7401'},
    [Shop Date]={"<'$(vSelectedOPMthDate)'"}
   >}[Hr Clocked])

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
New Contributor III

Re: Date filtering in set analysis for chart not working

really appreciate your help and quick response!

 

unfortunately it still does not work. Once I add in the portion to do with the [Shop Date] in the set analysis, the entire data set goes missing for that Group. 😞

Highlighted
Employee
Employee

Re: Date filtering in set analysis for chart not working

can you change your load script to format the [Shop Date] by loading this way:

Load Date([Shop Date]) as [Shop Date],

.....

It does seem a mismatch of data format