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)
17 Replies
Sellls
Contributor III
Contributor III
Author

Just tried that but it did not work either.

 

For troubleshooting purposes I tried to just substitute the variable with a fixed value of (today(2)+100), and even this made the bars on the chart disappear.

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

 

Which makes me believe something is wrong with the whole ,[Shop Date]={"<(today(2)+100)"}  or [Shop Date]={"<$(vSelectedOPMthDate)"} expression that I add in, since sum({$<[Group]={'7401'}>}[Hr Clocked]) alone works fine.

 

 

 

Vegar
MVP
MVP

Is your Shop Date numeric?

How does a filterbox with the field look like? Is it left or right aligned?

image.png

What happens if you use greater than  (>) instead of less (<) than in your set expression?

Sellls
Contributor III
Contributor III
Author

Qlik help 2.PNG

 

It's right handed but shows up in date format. I did the load with the date([Shop Date]) as [Shop Date] per a previous recommendation above.

Same thing happens when I use > greater than instead of < smaller than - the bars on the chart disappear and go to zero.

Vegar
MVP
MVP

Try this, notice the 

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

Sellls
Contributor III
Contributor III
Author

I've just tried that as well, but it still fails to work and the entire series goes down to zero. 😞

Lisa_P
Employee
Employee

If you can share a .qvf file with us, it would be easier to troubleshoot

Sellls
Contributor III
Contributor III
Author

Hi  Lisa_P,

Thanks for the suggestion, I'm still trying to get my administrator to pull the file out for me as it is currently on the hub server which I don't have direct access to at the moment.

However, just wanted to share that if I replaced the variable with a hard code date such as "30/06/2020", it works and the bar shows. But once I change it to "today(2)+100" or the variable "$(vSelectedOPMthDate)", it no longer works.

Is there an issue with Qliksense reading variables or date functions within set analysis?

 

In the meantime, will work on getting the qvf file out and sharing it if possible!

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!