Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

alextomlins
New Contributor II

Show the last 12 weeks of Current Day of the Week

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

5 Replies
RonaldDoes
Contributor III

Re: Show the last 12 weeks of Current Day of the Week

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
New Contributor II

Re: Show the last 12 weeks of Current Day of the Week

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

Re: Show the last 12 weeks of Current Day of the Week

Hi Alex,

Try:

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

alextomlins
New Contributor II

Re: Show the last 12 weeks of Current Day of the Week

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

Re: Show the last 12 weeks of Current Day of the Week

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

Community Browser