10 Replies Latest reply on Jun 11, 2018 7:43 PM by ambrose chen

# How to fill the values by date

Could someone help me to accomplish the partial data sheet from raw date as below:

If the Date

before 2017/9/1 then replace the null to 2,000

from  2017/9/1 to 2018/1/31 then 1,000

after 2018/1/31 then 1,000

In the raw data, the set points of Date may vary from one to 5 or 6.

So, how do I create a calculated field to fill the values?

Thanks a lot !

• ###### 1. Re: How to fill the values by date

Hi,

IF [Date]<=MAKEDATE(2017,9,1) THEN 2000

ELSEIF [Date]>MAKEDATE(2017,9,1) AND [Date]<=MAKEDATE(2018,31,1) THEN 1000

ELSEIF [Date]>MAKEDATE(2018,31,1) THEN 1000

ELSE [Points]

END

Note that the IF condition will follow the preceding order of the conditions in which the conditions are written. i.e if First condition id true it will not validate the ELSEIF and throw the THEN value of that condition. IF First condition is false only then it will go to the Second condition and so on.

Regards

Sohan.

• ###### 2. Re: How to fill the values by date

Hi Ambrose,

Will this work? -

if [Date]< date('2017-09-01') then 2000

elseif [Date]>= DATE('2017-09-01' ) and [Date]<= DATE('2018-01-31') then 1000

elseif [Date]>date( '2018-01-31' )then 1000

END

I've shown the value as 'min' aggregation just to show you how it works above. As there are two rows of data for some dates, e.g. 01/09/2017, it will show as 2000 if I summed it up.

Attached the workbook so that you can see.

Thanks,

Mavis

• ###### 3. Re: How to fill the values by date

Hi, Ambrose

Please find my solution attached as well as result below

Hope this helps

ZZ

• ###### 4. Re: How to fill the values by date

Sohan,

Thank you very much for your help!

The result is exactly what I expected,

but maybe I need to include "Prod_id" column to make the question more clearly.

Date Prod_id Points

2017/7/1 A001 2000

2017/9/1 A001 1000

2018/2/1 A001 1000

2017/6/23 A001

2017/6/30 A001

2017/9/8 A001

2018/2/2 A001

2018/2/13 A001

2018/2/23 A001

2017/9/1 J093 2000

2017/10/1 J093 1000

2017/12/1 J093 1000

2018/3/1 J093 1000

2017/9/15 J093

2017/9/22 J093

2017/9/29 J093

2017/10/6 J093

2017/12/8 J093

2017/12/15 J093

2018/2/23 J093

2018/3/2 J093

2018/3/9 J093

Best Regards

Ambrose

• ###### 5. Re: How to fill the values by date

Yes you may need to include the Prod_id if the data is at that level i.e if Prod_id is the unique identifier in the data.

• ###### 6. Re: How to fill the values by date

Hi  Mavis Liu, Zhouyi Zhang

Thank you very much for your help to let me know how to fill the data.

If I add another column, Prod_id, to my data sheet, how do  i do to fill the data?

The new data like this:

 Date Prod_id Points 2017/9/1 A001 1000 2018/2/1 A001 1000 2017/7/1 A001 2000 2017/6/23 A001 2017/6/30 A001 2017/9/8 A001 2018/2/13 A001 2018/2/2 A001 2018/2/23 A001 2017/10/1 J093 1000 2017/12/1 J093 1000 2018/3/1 J093 1000 2017/9/1 J093 2000 2017/10/6 J093 2017/12/15 J093 2017/12/8 J093 2017/9/15 J093 2017/9/22 J093 2017/9/29 J093 2018/2/23 J093 2018/3/2 J093 2018/3/9 J093

Best Regards

Ambrose

• ###### 7. Re: How to fill the values by date

Hi, Ambrose

The same, nothing needs to be changed

ZZ

• ###### 8. Re: How to fill the values by date

Hi ZZ,

The problem is the time frame of prod_id J093 is different than A001,

it has 4 set points

2017/9/1      2,000

2017/10/1    1,000        before  2017/10/1                          then Points =2,000

2017/12/1    1,000        before  2017/12/1   after  2017/10/1                   1,000

2018/3/1      1,000        before  2018/3/1     after 2017/12/1                    1,000

after    2018/3/1                                                  1,000

Hope that would made the question more clearly.

B.R.

Ambrose

• ###### 9. Re: How to fill the values by date

Hi, Ambrose

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 10. Re: How to fill the values by date

Hi ZZ,

Thanks a lot, very appreciated.

Ambrose