Show the last 12 weeks of Current Day of the Week

[alextomlins]

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

[RonaldDoes]

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

[alextomlins]

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

[RonaldDoes]

Hi Alex,

Try:

Num(WeekDay(YOURDATEFIELD)) AS WKDYValue in your script, that way you'll have it stored numerically.

[alextomlins]

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

[RonaldDoes]

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])