Nested if statment

[julianhoetzel]

Hi guys, 

I have two issues to solve:

1) I need a double conditional statement (nested if statement)

2) I need the clients only to be added up that are existent in the file. Otherwise they should not be added up in the date structure (Missing amounts of Saturday and Sunday become Friday values)

Example: 

Date	Day	Amount	FileName
2021-01-01	Friday	10	FileName20210101
2021-01-04	Monday	30
2021-01-05	Tuesday	40	FileName20210105

So the output file should only include values if a FileName is included on the date, therefore excluding Monday from the file and from any metrics.

Date	Day	Amount	FileName
2021-01-01	Friday	10	FileName20210101
2021-01-02	Saturday	10	FileName20210101
2021-01-03	Sunday	10	FileName20210101
2021-01-05	Tuesday	40	FileName20210105

The multirow formula looks like this:

So i need some if statement that says

if contains([FileName]=shows something then if (datetimediff([Row+1:Date],[Date],"days"))>1
then datetimeadd([Row+1:Date],-1,"days") else Date endif

Hope that makes sense.

Cheers

Julian

[mceleavey]

Hi @julianhoetzel ,

I've built this using a generate rows tool to create those missing dates, and then determined the days of the week for those missing dates using a simple formula.

I then used a tool I built which auto-fills the missing values, leaving everything populated as you wanted, then it was a simple matter of filtering out the unwanted rows:

I've attached the exported workflow, and the manual files if you are on an older version.

I hope this helps,

M.