Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Sellls
Contributor III
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 (1)
1 Solution

Accepted Solutions
Sellls
Contributor III
Contributor III
Author

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
Lisa_P
Employee
Employee

You'll need double quotes for < comparison ..

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

Sellls
Contributor III
Contributor III
Author

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?

Vegar
MVP
MVP

What's the definition of your variable vSelectedOPMthDate?

 

Lisa_P
Employee
Employee

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

Sellls
Contributor III
Contributor III
Author

(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.

Sellls
Contributor III
Contributor III
Author

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.

Vegar
MVP
MVP

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

Sellls
Contributor III
Contributor III
Author

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. 😞

Lisa_P
Employee
Employee

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