Loadscript - creating a dimension with nested if regarding dates

[Daryn]

Hi all,

Sorry for the vague subject.

But, I want to create a new field to use as a dimension based on a date field, where;

1 - The date is within this week 

2 - The dates are then shown as Monday, Tuesday .. through Friday.

I created the dates as Mon - Friday using;

IF(WeekDay ([Planned Picking Date]) =0, 'Monday',
IF(WeekDay ([Planned Picking Date]) =1, 'Tuesday',
IF(WeekDay ([Planned Picking Date]) =2, 'Wednesday',
IF(WeekDay ([Planned Picking Date]) =3, 'Thursday',
IF(WeekDay ([Planned Picking Date]) =4, 'Friday'))))) AS [This Week],

This produced when using a count on another field as the measure   =Count(Delivery);

But this doesn't limit  to the current week data.

So I tried;

    IF([Planned Picking Date] =Week(today()), IF (WeekDay ([Planned Picking Date]) =0, 'Monday',

    IF([Planned Picking Date] =Week(today()), IF (WeekDay ([Planned Picking Date]) =1, 'Tuesday',

    IF([Planned Picking Date] =Week(today()), IF (WeekDay ([Planned Picking Date]) =2, 'Wednesday',

    IF([Planned Picking Date] =Week(today()), IF (WeekDay ([Planned Picking Date]) =3, 'Thursday',

    IF([Planned Picking Date] =Week(today()), IF (WeekDay ([Planned Picking Date]) =4, 'Friday')))))))))) AS [This Week],

This loaded ok, but produced blank charts

I tried and switched the if's around, but the same blank results.

Is it possible, am I overcomplicating it? 

Thanks in advance, hope I have supplied enough info.

Regards  as always,

Daryn

[RsQK · Accepted solution]

Hi, this might just work - sample script:

LET vMin = FLOOR(MAKEDATE(2022,1,1));
LET vMax = FLOOR(MAKEDATE(2022,12,31));

temp:
LOAD
$(vMin) + RECNO() - 1 AS [Planned Picking Date]
AUTOGENERATE $(vMax)-$(vMin)+1;

temp2:
LOAD
CEIL(RAND()*100) AS Sales,
CEIL(RAND()*ROWNO()) AS Delivery,
DATE([Planned Picking Date]) AS [Planned Picking Date],
IF([Planned Picking Date] >= FLOOR(WEEKSTART(TODAY())) AND [Planned Picking Date] <= WEEKEND(TODAY())-2,WEEKDAY([Planned Picking Date])) AS [This Week]
RESIDENT temp;

DROP TABLE temp;

I created a pie chart with:

- measure : SUM(Delivery)

- dimension: [This Week] (null values checked out in the properties).

Other approach would be creating a flag for "this week" and using said flag in set analysis.

[ThiagoCN]

Hello Daryn

How is your date field, doesn't' make sense create a master calendar Or a Autocalendar??

Master calendar: Creating A Master Calendar - Qlik Community - 341286
Auto Calendar: How to create on your own an Autocalendar (Derived... - Qlik Community - 1716542

When you can use fields directly from the master calendar or from auto calendar.

[Daryn]

Sorry, my bad explanation/terminology.

The date 'Picking Date' is an auto calendar date.

[Daryn]

Hi,

Thanks this certainly appears to work, I just need to check the data 🙂 Then if all good I will mark as solution.

Regards, Daryn

[rwunderlich · Accepted solution]

I don't think you need a condition for every day of the week. If you are ok with the three digit abbreviation for weekday (Sat, Sun, etc) then you can use just the Weekday() function. Weekday returns a Dual with the three character name. 

IF(InWeek([Planned Picking Date],Today(),0), WeekDay([Planned Picking Date])) AS [This Week]

If you require the full weekday name (Saturday, Sunday, etc) then I would do it like this:

IF(InWeek([Planned Picking Date],Today(),0), Date([Planned Picking Date], 'WWWW')) AS [This Week]

Updated to use Marco's suggestion for InWeek().

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[MarcoWedel]

To test for [Planned Picking Date]  lying inside the current week you can also use the InWeek() function
(https://help.qlik.com/en-US/sense/August2022/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeF...)

i.e. like

InWeek([Planned Picking Date],Today(),0)

another possible test would be

WeekStart([Planned Picking Date])=WeekStart(Today())