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
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.
Hi Praveen, Iโll give it a go but itโs quite beyond meโฆ
Cheers
Steve got to it before I could finish typing.
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.
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:
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)
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.
Whoops! I used DATE() where it should have been TODAY(). Iโve corrected my earlier post.
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
Hi @Steve,
A sleek expression and very well explained.
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
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.
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
Guys. How would I add [JobStatus]=โIN-PROGRESSโ to theโฆ
[ECD]=(TODAY()- WEEKDAY(TODAY()) + 2 + (7 * IF((WEEKDAY(TODAY()) > 2), 1, 0)))
Cheers
Hi @Dave_Willett,
Could you please try
AND( [ECD]=(TODAY()- WEEKDAY(TODAY()) + 2 + (7 * IF((WEEKDAY(TODAY()) > 2), 1, 0))), [JobStatus]=โIN-PROGRESSโ)
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.
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.
User | Count |
---|---|
37 | |
26 | |
23 | |
17 | |
14 |