AnsweredAssumed Answered

How to check if a date falls under multiple lists of dates and days then perform a calculation?

Question asked by makintosh on Feb 7, 2018
Latest reply on Feb 8, 2018 by makintosh

How do I compute a value (Rate) by first checking if a (Date) falls under 2 lists of dates (ListA) and (ListB) and (Day(s)) of the week, then perform a calculation that will multiply an (Amount) with a (Multiplier)? Amount is a field in a parent table and Rate and Date is in a child table.

 

Inputs for ListA are:

1/1

29/3

30/3

9/4

1/5

12/6

27/8

30/11

25/12

30/12

 

For ListB:

16/2

25/2

31/3

21/8

1/11

2/11

24/12

31/12

 

For Days:

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Saturday & Sunday

 

For Multiplier:

1.3

1.5

2.0

2.6

3.0

 

I tried making several case statements placing the dates, days and multipliers one by one but couldn't seem to make it work. I made another table that contains ListA, ListB, Days and Multiplier but I'm quite lost as to how to link them to (Rate) and make the correct calculation. Same with the thought of putting the values in a value list.

 

Any help is greatly appreciated.

Outcomes