Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

birkirbjorns
New Contributor III

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

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

Tags (3)
1 Solution

Accepted Solutions
stigchel
Honored Contributor

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

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?


5 Replies
stigchel
Honored Contributor

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

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.

birkirbjorns
New Contributor III

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

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
Honored Contributor

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

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?


birkirbjorns
New Contributor III

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

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 QlikviewSmiley Very Happy

Again thanks for your help.

Regards

Birkir

stigchel
Honored Contributor

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

You are welcome