Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Trying to set multiple conditions for the button. However, the syntax is not working correctly. 😕
I have a Week field which need to be selected as past 4 weeks, eg. Today is Week10, the button need to select Week6,7,8,9.
It works if value cell is,
6;7;8;9 without conditions just raw number, or
=Week(Today())-4 just one condition.
However, it failed when tried with =Week(Today())-4; Week(Today())-3; Week(Today())-2; Week(Today())-1
Anyone have idea what wrong with the syntax?
Hi, you can try adding $(=):
='$(=Week(Today())-4);$(=Week(Today())-3);$(=Week(Today())-2);$(=Week(Today())-1)'
You'll need to add some login for year start, in example if you are on week 3 where 3-4 will return -1.
Hi, you can try adding $(=):
='$(=Week(Today())-4);$(=Week(Today())-3);$(=Week(Today())-2);$(=Week(Today())-1)'
You'll need to add some login for year start, in example if you are on week 3 where 3-4 will return -1.
Million thanks, your syntax is working for the week selection!
Figuring the login for year start as the data have multiple years, yet to get any ideas. 😰
Hi, about the logic... at the start of each year it has to access the last weeks of the previous year or just the weeks it has for the current year?
it has to access the last weeks of the previous year, eg. today is W3, it has to return W51,W52,W1,W2.
Hi, I think the best option here is to use an autonumeric for weeks, as each year can have diffrent weeks we can't use the months logic to assign consecutive numbers (Year*12+Month).
And as you are mixing differnt years it work work if you only select week, becuase ther will be different Week1, one for each year, so you'll need ,make selctings in a field that joins week and year.
You can do a load of the table where the week is, sorted by year-week, or by date and add the WeekSecuencial field, something like:
LOAD
*,
If(WeekName(DateField)=WeekName(Peek(DateField))
,Peek(WeekSecuencial)
,RangeSum(1,Peek(WeekSecuencial))
) as WeekSecuencial,
WeekName(DateField) as WeekName
Resident DataTable
Order By DateField;
DROP DataTable;
And assign values as:
='$(=Only({<WeekSecuencial={$(=Max(WeekSecuencial )-4)}>} WeekName);$(=Only({<WeekSecuencial={$(=Max(WeekSecuencial )-3)}>} WeekName);$(=Only({<WeekSecuencial={$(=Max(WeekSecuencial )-2)}>} WeekName);$(=Only({<WeekSecuencial={$(=Max(WeekSecuencial )-1)}>} WeekName);$(=Only({<WeekSecuencial={$(=Max(WeekSecuencial ))}>} WeekName)'
Not tested.