topic Re: Loadscript - creating a dimension with nested if regarding dates in App Development

Loadscript - creating a dimension with nested if regarding dates

Daryn — Wed, 19 Oct 2022 12:47:33 GMT

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

Re: Loadscript - creating a dimension with nested if regarding dates

RsQK — Wed, 19 Oct 2022 12:59:34 GMT

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.

Re: Loadscript - creating a dimension with nested if regarding dates

ThiagoCN — Wed, 19 Oct 2022 12:59:49 GMT

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.

Re: Loadscript - creating a dimension with nested if regarding dates

Daryn — Wed, 19 Oct 2022 13:27:09 GMT

Sorry, my bad explanation/terminology.

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

Re: Loadscript - creating a dimension with nested if regarding dates

Daryn — Wed, 19 Oct 2022 13:28:44 GMT

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

Re: Loadscript - creating a dimension with nested if regarding dates

rwunderlich — Wed, 19 Oct 2022 23:18:13 GMT

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

Re: Loadscript - creating a dimension with nested if regarding dates

MarcoWedel — Wed, 19 Oct 2022 21:50:13 GMT

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/DateAndTimeFunctions/inweek.htm)

i.e. like

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

another possible test would be

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