topic Re: Date Variables in Set Analysis in App Development

Date Variables in Set Analysis

sgagliardi — Tue, 29 Dec 2020 20:11:23 GMT

Hello Qlik Community,

Utterly stumped with an issue today using variables in set analysis, particularly those around dates

Example:

I have two variables, one that establishes the most recent month in my data set, and another that establishes the previous quarter. The second one makes use of the first.

let ThisMonth = 'date(Max(TOTAL date([Service Date])),"MMM-YYYY")';

let CurrentQuarter = '>=$(=MonthStart($(ThisMonth),-2))<=$(=Date(Floor(MonthEnd($(ThisMonth),-0))))';

What I would like to do is sum cost for the latest quarter like so:

=sum({<[Service Date]={"$(=Date($(CurrentQuarter)))"}>}[Cost])

I cannot get it to work though. I've tried many approaches that I have come across in the community, but none seem to work for me.

Interestingly, the following works perfectly fine:

=sum({<[Service Date]={">=$(=MonthStart($(ThisMonth),-2))<=$(=Date(Floor(MonthEnd($(ThisMonth),-0))))"}>}[Cost])

I'm beginning to wonder if the variable being utilized within the other variable has something to do with why it's not working for me -- however, when I replace the $(ThisMonth) variable with its expression, it does not work for me either.

Any insight here would be awesome.

Re: Date Variables in Set Analysis

tresB — Wed, 30 Dec 2020 05:34:28 GMT

I guess quotes are playing evil here. Try removing the single quotes from the ThisMonth variable definition.

Re: Date Variables in Set Analysis

marcus_sommer — Wed, 30 Dec 2020 14:36:54 GMT

It didn't work because your nested variables contain commas which will be always treated as parameter-delimiter. AFAIK there is no way to mask them or to bypass this behaviour unless you could ensure with a different logic/syntax that there no commas included.

Beside this it's not recommended to use nested variables because of the complexity of the matter - not only the comma-issue else also in regard to the various kinds of quotes, comments, $-sign expansions and probably some more reasons.

Even the creating of those variables itself is questionable because variables should simplify things which they don't in this case. I suggest to avoid them and using an appropriate field from the datamodel, maybe like:

year(DATE) * 12 + month(DATE) as RunningPeriod

and then querying the max. value within the set analysis +- n Periods for those periods you want to display.

- Marcus