topic Qlik Sense set analysis with date variable less than or equal to in App Development

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

Anonymous — Sat, 28 Mar 2015 14:58:51 GMT

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

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

stigchel — Sat, 28 Mar 2015 15:10:16 GMT

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.

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

Anonymous — Sat, 28 Mar 2015 15:20:15 GMT

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

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

stigchel — Sat, 28 Mar 2015 15:44:23 GMT

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?

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

Anonymous — Sat, 28 Mar 2015 19:35:19 GMT

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

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

stigchel — Sat, 28 Mar 2015 20:09:37 GMT

You are welcome