Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
First post here and very new at qlik. I am trying to get the last 12 Mon, Tue, Wed etc based on current date. So for example today is Tuesday and so in the chart you would see a bar of Sales/Revenue GBP for the last 12 weeks.
I haven't started on the last 12 stuff but i was working on;
Sum({$<[WKDYValue]= {weekday(today(0))}>}[Revenue GBP])
(WKDYValue is a column converting my main date into Mon, Tue, Wed in the load it is set as ...Weekday([Date]) As WKDYValue,)
I am very very new to this and have only been properly learning for a few weeks - I'm not actually a coder in any way so bear with me
Thanks,
Alex
Hi Alex,
How about:
=Sum({<[WKDYValue] = {$(=Weekday(Today())), DATEFIELD = {'>$(Date(Today(),-(12*7))'}>} [Revenue GBP])
This basically includes weekdays that are equal to todays weekday and dates that are greater than 12 weeks ago.
Hope this helps you.
With kind regards,
Ronald
Hi Ronald,
Thanks for your reply. Unfortunately it's still not working. I think the problem is that =Weekday(Today()) is returning a numerical value (0-6) and WKDYValue is in the data load as 'Mon, Tue, Wed' etc.
I think it's because weekday produces a dual value? So i could either change the load to come out with a numerical value or get the sheet expression to be in 'Mon, Tue, Wed'
I am very new so maybe i'm miles off but that's what i'm thinking.
Kind regards,
Alex
Hi Alex,
Try:
Num(WeekDay(YOURDATEFIELD)) AS WKDYValue in your script, that way you'll have it stored numerically.
So that worked in the data load and now WKDYValue is 0-6 however none of my expressions seem to work as I would like.
I am now just trying to get the last 5 days of the week. So far it has left me with;
Sum({<[WKDYValue] = {$(=Weekday(Today(0)))}>}[Revenue GBP])
Weekday(Today(0))) leaves me with a numeric value same as in the data load - very confused about why this is not working. It's driving me mad.
Either way thank you for your help
Hi Alex,
This just got interesting. Are you able to share your QVW (or an example with some made-up data) and describe what it is you are expecting?
I'm certain we'll work it out.
In the meanwhile, try adding apostrophies like below.
Sum({<[WKDYValue] = {'$(=Weekday(Today(0)))'}>}[Revenue GBP])