Hi,
I have the following data, which I need to replicate/generate from Alteryx:
Basically I need help on how I generate the week columns using the start and end dates.
In excel the calculations are as follows:
'this week' is: =IF(A19<TODAY(),F19,"")
WK1: =IF(A5<TODAY()+14,IF(B5>TODAY()+7,F5,""),"")
WK2: =IF(A5<TODAY()+21,IF(B5>TODAY()+14,F5,""),"")
WK3: =IF(A5<TODAY()+28,IF(B5>TODAY()+21,F5,""),"")
and so on.
Any ideas of suggestion gratefully received.
Solved! Go to Solution.
Please use the below formula.
WK1: =IF(A5<TODAY()+14,IF(B5>TODAY()+7,F5,""),"")
IF [A5]<datetimeadd(DateTimeToday(),14"days")
AND [B5]>datetimeadd((DateTimeToday(),7"days")
THEN [A5]
ELSE ""
ENDIF
Many thanks
Shanker V
@Julie_Clarke updated the formula, you can replicate the WK2 and WK3 from WK1
'this week' - IFF([start date] < DateTimeToday(),[resource per week],"")
WK1: -- IIF([start date] < DateTimeAdd(DateTimeToday,14,'day'),IIF([finish date] > DateTimeAdd(DateTimeToday,7,'day'),[resource per week],""),"")
IF(A19<TODAY(),F19,"")
Use the below formula,
IF ([A19]<datetimetoday())
THEN [F19]
ELSE ""
ENDIF
Using this formula will bring all the dates < today hence, I will suggest to use rephrase the formula for 7 days.
Many thanks
Shanker V