Date flags used when using the date picker widget

[Anonymous]

Hi guys,

I hope you are doing well! I am totally confused with flags.

Situation: I have three separate tables, for the argument sake, I uploaded a cut version of one. All three tables I merged with the col TimeStamp as I am using on date picker calendar i.e. one KPI is calculated based on one table, another, based on 2nd and third, based on the 3rd table. All three KPIs are on the same page and supposed to be changed at the same time as soon as a person selects, e.g. Last 7 days from the date picker. For this reason, I used Timestamp for of these tables.

My problem is that I cannot link the flags with the expression and calendar. Select the date in the calendar and nothing change in the KPI. What am I doing wrong? Is it timestamp? I have reviewed - I believe - all threads about it but did not find anything similar.

Here is the upload script:

TempCalendar:

LOAD Date(Floor(TimeStamp)) AS Date,

TimeStamp,

Hour(TimeStamp) AS Hour,

Minute(TimeStamp) AS Minute,

If(TimeStamp>=(Today()-30) AND TimeStamp<Today(), 1, 0) AS Is30DayPeriod,

If(TimeStamp=Week(Today()), 1, 0) AS IsCurrentWeek;

LOAD

    Timestamp($(vMinDate) + (RecNo() - 1)/1440) as TimeStamp

AUTOGENERATE 1440 * $(vDays);

Gate:

LOAD

[idgate],

[TRACTOR_ARRIVE_DATE],

[TRACTOR_DEPART_DATE] as TimeStamp

FROM [lib://Desktop/gate_4.xlsx]

(ooxml, embedded labels, table is Sheet2);

Shift:

LOAD

    idvessl,

    EST_ARV_D  As TimeStamp

FROM [lib://Desktop/vessels_3.xlsx]

(ooxml, embedded labels, table is Sheet1);

Here is the expression I used:

count({<IsCurrentWeek={1}}idgate)

[juraj_misina]

I believe there's a typo in your expression:

count({<IsCurrentWeek={1}>}idgate)

Plus your IsCurrentWeek is always =0 because its' definition is wrong. It should be

If(WeekName(TimeStamp)=WeekName(Today()), 1, 0) AS IsCurrentWeek;

or

-InWeek(TimeStamp, Today(), 0) as IsCurrentWeek;

Hope this helps.

[Anonymous]

Hi Juraj,

Thank you for the response, but the question still remains. In the date range picker, one could choose:

-Today;

-Yesterday;

-Last month;

-Last 30 days;

-This month;

-This week;

-Custom range.

So the idea is that when a person chooses 'Today', show the KPI performance as of today along with the yesterday performance; when chooses 'This week', show this week performance vs last week performance, when 'This month', show this month and last month performance. All the solution I have reviewed were more like static where you do set analysis, e.g. Count({<[TimeStamp.autoCalendar.Date] = {"$(=Date(Today()-1))"}>}CNTR). So my question is (1) how to make it work for the Calendar and secondly, what would be your advice about the above scenario implementation? I was thinking to use if statement.

[MK9885]

In your Date Range extension use 'Date' as dimension.

Don't use Timestamp.

Try below attachment.

It would be much easier if you can create a Master Calendar to get Year, Month, Week or even previous month flags etc...

Could you also please provide the data in excel file to reload the app?

[juraj_misina]

Hi,

I usually build on the solution which you call static, but I make it dynamic with a variable and the qsVariable extension. Following your example I use following measure

Count({<[TimeStamp.autoCalendar.Date] = {">=$(=Date(Today()-$(vDayCount)))"}>}CNTR)

and I preset qsVariable extension with desired values for vDayCount variable (0=Today, 7=Last 7 days, etc.)

Hope this helps

Juraj

[Anonymous]

Hi Juraj,

Thank you for the reply! How did you define vDayCount?

[Anonymous]

So, where I am now, I removed some options from the calendar and left just with Today, This Week, This month and custom range.

Secondly, I am using nested Ifs, like if(isToday = 1, 1,

                                                                 if(isThisWeek = 1, 2,

                                                                         if(isThisMonth = 1, 3, 4))).

So far testing gave good results. Now I just need to replace 1 with the calculation of the yesterday's figures, 2 with the calculation of the last week and 3 with the calculation of the last month. For the last week and last month, I will use

Count( { 1<  [TimeStamp.autoCalendar.WeekRelNo] = {0}> } [CNTR_gate] ) and  Count( { 1< [TimeStamp.autoCalendar.MonthsAgo]={1} > } [CNTR_gate] ).

What is left is yesterday's figures when selected Today in the calendar picker.

[Anonymous]

Hi,

Thank you for the response... but I use a Temp as a MasterCalendar, don't I?

[MK9885]

You have auto generate master calendar which is a basic one.

Master Calendar Script‌

Copy paste the script within that word doc and replace the var dates as per your own need, also the date format.

This would be easier cus you would just need to have 1 or 0 in front end for last month or last year etc...

[Anonymous]

I still don't follow you...sorry, I am not very experienced.

Here I am creating a master calendar using TempStemp col

TempCalendar:

LOAD Date(Floor(TimeStamp)) AS Date,

TimeStamp,

Hour(TimeStamp) AS Hour,

Minute(TimeStamp) AS Minute,

If(TimeStamp>=(Today()-30) AND TimeStamp<Today(), 1, 0) AS Is30DayPeriod,

If(TimeStamp=Week(Today()), 1, 0) AS IsCurrentWeek;

LOAD

    Timestamp($(vMinDate) + (RecNo() - 1)/1440) as TimeStamp

AUTOGENERATE 1440 * $(vDays);

I am using Autogenerated because after testing I realized that it works better.