Complicated date Expression for Slice

Dave_Willett · 10-19-2019 12:05 PM

I use a large TV in our workshop which reads data from a csv, works great and rotates dates depending on dates.

The following expressions are from Microsoft Access.

=Date()-Weekday(Date(),2)-7*(Weekday(Date(),2)>1)+1 'Will return Monday’s date of the current week, if Monday has elapsed it will give next Monday’s date instead.

=Date()-Weekday(Date(),2)-7*(Weekday(Date(),2)>2)+2 'Will return Tuesday’s date of the current week, if Tuesday has elapsed it will give next Tuesday’s date instead.

And so on. The TV database refreshes and after midnight the dates change automatically.
I need the same for a slice but in AppSheet. I tried the following:

[ECD]=TODAY()-Weekday((TODAY(),2)-7)*(Weekday((TODAY(),2)>1))+1

Can any of you guys help in making this work?
Much appreciated

pravse

Hi @Dave_Willett, have you tried using the expression Test button (it is in the expression/formula builder dialog where you type in the formula)? It will show you the value of this expression for different rows in the table. And it will let you explore the values for each subexpression so that you can “debug” it.

Dave_Willett

Hi Praveen, I’ll give it a go but it’s quite beyond me…
Cheers

Bahbus

Steve got to it before I could finish typing.

Steve

I believe (untested!) the following is the AppSheet equivalent of “return Monday’s date of the current week, if Monday has elapsed it will give next Monday’s date instead”:

(
  TODAY()
  - WEEKDAY(TODAY())
  + 2
  + (7 * IF((WEEKDAY(TODAY()) > 2), 1, 0))
)

Start with the current date: TODAY()
Rewind the date to the previous Sunday by subtracting the current weekday number-of-days from the current date (1): - WEEKDAY(TODAY())
Advance the date from the previous Sunday (2) to this week’s Monday (in AppSheet, Monday is day 2 of the week): + 2
Ask “is today’s weekday later than Monday?”: (WEEKDAY(TODAY()) > 2)
If today’s weekday is later than today (4), give 1, otherwise give 0.
Multiply 7 by the value given by (5).
Advance the this week’s Monday date (3) by the value computed in (6). If Monday has passed already, (6) will give (7 * 1) = 7, which will advance the date from (3) to Monday of next week; otherwise, (6) will give (7 * 0) = 0, which will leave the date at Monday of this week.

Dave_Willett

Thanks guys. But how do I evaluate the expression to the ECD column? The ECD column holds dates in uk format like 20/10/2019. There is a slice for Mon, Tues, Weds and so on, so each slice needs to return the relevant day from the ECD and roll filter forward if we are passed that date.

Here is the design of the Access database I need to recreate:

Which returns filtered records:

Its a dashboard which would give me this function but evaluating the ECD is the tricky bit.

Suvrutt_Gurjar

Hi @Dave_Willett,

Do following expressions help for daily slice for each of the day of the week as below

MONDAY
AND(WEEKNUM(TODAY())=WEEKNUM([EOD]), WEEKDAY([EOD])=2)
TUESDAY
AND(WEEKNUM(TODAY())=WEEKNUM([EOD]), WEEKDAY([EOD])=3)
WEDNESDAY
AND(WEEKNUM(TODAY())=WEEKNUM([EOD]), WEEKDAY([EOD])=4)
THURSDAY
AND(WEEKNUM(TODAY())=WEEKNUM([EOD]), WEEKDAY([EOD])=5)
FRIDAY
AND(WEEKNUM(TODAY())=WEEKNUM([EOD]), WEEKDAY([EOD])=6)
SATURDAY
AND(WEEKNUM(TODAY())=WEEKNUM([EOD]), WEEKDAY([EOD])=7)
SUNDAY
AND(WEEKNUM(TODAY())-1=WEEKNUM([EOD])-1, WEEKDAY([EOD])=1)

Dave_Willett

Hi Suvrutt, thats looking good with a minor correction. I can’t test this until Monday has elapsed. If I run this Tuesday 22nd, will Monday show the 28th?

Mon 21st, Tues 22nd, Weds 23rd, Thurs 24th, Fri 25th, Sat 26th.
And once Mon 21st has elapsed the results should be:
Mon 28th Tues 22nd, Weds 23rd, Thurs 24th, Fri 25th, Sat 26th.

Steve

Whoops! I used DATE() where it should have been TODAY(). I’ve corrected my earlier post.

Dave_Willett

And:
[ECD]=(TODAY()- WEEKDAY(TODAY()) + 3 + (7 * IF((WEEKDAY(TODAY()) > 3), 1, 0)))
For Tuesday

[ECD]=(TODAY()- WEEKDAY(TODAY()) + 4 + (7 * IF((WEEKDAY(TODAY()) > 4), 1, 0)))
For Wednesday…

etc etc?

Awesome Guys

Suvrutt_Gurjar

Hi @Steve,

A sleek expression and very well explained.

Fernando_Lopez1

I guess there is a parenthesis out of place and adding an IF()…

[ECD]=TODAY() - WEEKDAY(TODAY()) + IF( WEEKDAY(TODAY()) > 1), 7, 0) + 1

I’ve didn’t tested… but I guess it works

Suvrutt_Gurjar

Hi @Dave_Willett,

Got it- you need a rolling over of weekdays into next week every day. The expression given by me will work for one week. I will add the rollover logic and update later.

In the mentime , please go ahead with testing the expression given by @Fernando_Lopez as his expression is a compact one. Mine one will become a bit longer after adding rollover week logic.

Dave_Willett

Really appreciate the help on this guys, it is complicated by yes Suvrutt you got it right, each day needs to roll over as you say. Looking forward to your version too.
Kind Regards

Dave_Willett

Dave_Willett

Guys. How would I add [JobStatus]=“IN-PROGRESS” to the…
[ECD]=(TODAY()- WEEKDAY(TODAY()) + 2 + (7 * IF((WEEKDAY(TODAY()) > 2), 1, 0)))

Cheers

Suvrutt_Gurjar

Hi @Dave_Willett,

Could you please try
AND( [ECD]=(TODAY()- WEEKDAY(TODAY()) + 2 + (7 * IF((WEEKDAY(TODAY()) > 2), 1, 0))), [JobStatus]=“IN-PROGRESS”)

Dave_Willett

Looks good to me Suvrutt… wish I could get my head around these expressions. I realise there are articles about them but they don’t suit every scenario.

Once again, thank you.

Suvrutt_Gurjar

Hi @Dave_Willett,

You are welcome. I am sure it is matter of just small time by which we all get used to expressions on this versatile and user friendly platform of AppSheet.