Qlik Sense set analysis with date variable less than or equal to

[Anonymous]

Hi,

I have a set analysis calculates for a given period, it works fine when dates are hard coded but when I try to use variables I mess things up. I have tried many different things and read many threads here but I simply cannot not solve this. This is the set analysis

Sum({$<SaleDate={'2015-03-01'},RegDate={'<=2015-03-26'}>}SalesAmount)

The variables look like this

LET v_YesterDay = Date(Today()-1,'YYYY-MM-DD');

LET v_MonthStart = monthstart(Today());

So I am trying to use the variables in the set analysis instead of the hard coded dates. I just cant understand why I cant make it work.

All hints are greatly appreciated.

Regards

Birkir

[stigchel]

Like the hardcoded expression, the set analysis is expecting a string representation of the date in the same format as the field. Create a straight table with the expression, but leave out any label The heading should display how the variables are evaluated. My guess is that the monthstart is not in the correct format.

[Anonymous]

Hi,

Piet, thank you for your comment. I am also a bit pulsed on how to insert  the variable into the expression. Specially with the <= signs.  I am working in Qlik Sense, I cannot see the header you speak of.

Regards

Birkir

[stigchel · Accepted solution]

To be honest, I don't have experience in QlikSense, the set analysis should be the same though. With variables the expression should look like

Sum({$<SaleDate={'$(=v_MonthStart)'},RegDate={'<=$(=v_YesterDay)'}>}SalesAmount)

Regarding the header, is there no pivot and/or straight table chart with headers that display the label of the expression, and if not present display the expression instead in Qlik Sense?

[Anonymous]

Hi Piet,

Thank you so much this worked like I was planning. Thanks again.

There is a table and newly added pivot table, but I could not get this header thing going. Maybe it is different from Qlikview. My next step was to test this in Qlikview:D

Again thanks for your help.

Regards

Birkir

[stigchel]

You are welcome