Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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.
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
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?
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
You are welcome