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

    How to fill the values by date

    ambrose chen

      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 !

       

      螢幕快照 2018-06-11 下午9.18.12.png

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

          Hi,

           

          Please try the below.

           

          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
            Mavis Liu

            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

             

            2018-06-11_15h26_43.png

             

            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
              Zhouyi Zhang

              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
                ambrose chen

                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.

                Please read below:

                 

                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
                  Sohan Jawar

                  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
                    ambrose chen

                    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
                      Zhouyi Zhang

                      Hi, Ambrose

                       

                      The same, nothing needs to be changed

                       

                       

                      ZZ

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

                        Hi ZZ,

                         

                        Thank you for your reply.

                         

                        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
                          Zhouyi Zhang

                          Hi, Ambrose

                           

                          Please find my updates attached and below screenshot of result

                           

                           

                          Hope this helps

                           

                          ZZ

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

                            Hi ZZ,

                             

                            Thanks a lot, very appreciated.

                             

                            Ambrose