topic Re: Date filtering in set analysis for chart not working in App Development

Date filtering in set analysis for chart not working

Sellls — Mon, 20 Dec 2021 21:32:42 GMT

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!

Re: Date filtering in set analysis for chart not working

Lisa_P — Thu, 26 Mar 2020 05:39:20 GMT

You'll need double quotes for < comparison ..

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

Re: Date filtering in set analysis for chart not working

Sellls — Thu, 26 Mar 2020 06:13:20 GMT

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?

Re: Date filtering in set analysis for chart not working

Vegar — Thu, 26 Mar 2020 06:35:03 GMT

What's the definition of your variable vSelectedOPMthDate?

Re: Date filtering in set analysis for chart not working

Lisa_P — Thu, 26 Mar 2020 06:35:38 GMT

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

Re: Date filtering in set analysis for chart not working

Sellls — Thu, 26 Mar 2020 06:46:59 GMT

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

Re: Date filtering in set analysis for chart not working

Sellls — Thu, 26 Mar 2020 06:51:26 GMT

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.

Re: Date filtering in set analysis for chart not working

Vegar — Thu, 26 Mar 2020 07:12:06 GMT

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

Re: Date filtering in set analysis for chart not working

Sellls — Thu, 26 Mar 2020 07:30:41 GMT

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

Re: Date filtering in set analysis for chart not working

Lisa_P — Thu, 26 Mar 2020 07:36:37 GMT

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

Re: Date filtering in set analysis for chart not working

Sellls — Thu, 26 Mar 2020 07:55:46 GMT

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.

Re: Date filtering in set analysis for chart not working

Vegar — Thu, 26 Mar 2020 08:00:45 GMT

Is your Shop Date numeric?

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

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

Re: Date filtering in set analysis for chart not working

Sellls — Thu, 26 Mar 2020 08:11:20 GMT

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.

Re: Date filtering in set analysis for chart not working

Vegar — Thu, 26 Mar 2020 08:27:26 GMT

Try this, notice the

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

Re: Date filtering in set analysis for chart not working

Sellls — Thu, 26 Mar 2020 08:57:06 GMT

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

Re: Date filtering in set analysis for chart not working

Lisa_P — Fri, 27 Mar 2020 00:03:29 GMT

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

Re: Date filtering in set analysis for chart not working

Sellls — Fri, 27 Mar 2020 00:30:46 GMT

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!

Re: Date filtering in set analysis for chart not working

Sellls — Fri, 27 Mar 2020 00:49:44 GMT

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!