7 Replies Latest reply on Mar 29, 2016 9:47 AM by Jose Carreiras

    Calculated fields on dynamic date range

    Anna Chan

      Hi

       

      I am trying to get a calculated field to apply to only a range of dates, otherwise it would show another value, is that possible. Attached is my sample workbook.

       

      Thanks,

      Anna

        • 1. Re: Calculated fields on dynamic date range
          Derk Busser

          Anna,

           

          Can you be a little bit more specific as to your intentions with this calculation?

           

          Best regards,

           

          Derk

          • 2. Re: Calculated fields on dynamic date range
            Anna Chan

            I am trying to show the effect of campaign optimizations during a specific time period (ex if we applied x utils of advertising, PR, and Word of Mouth) during specific range of dates this equation will be applied.

            • 3. Re: Calculated fields on dynamic date range
              Anna Chan

              i created a calculated field where if date<reference then profit, if date>reference date then equation, but i am not getting the results i need. I want profit and profit multiplier to start at the same value, and when a certain date is selected in the parameter,  the formula would then apply to all date ranges greater than the reference date.

              • 4. Re: Calculated fields on dynamic date range
                Derk Busser

                Weird, but i cant open up your workbook. Gonna try tomorrow on my workstation...

                • 5. Re: Calculated fields on dynamic date range
                  Dan Zehner

                  I know this is probably a dead thread, but did you get this resolved? I have a similar issue!

                  • 6. Re: Calculated fields on dynamic date range
                    Carl Slifer

                    Create two parameters.

                    StartDate

                    EndDate

                     

                    Put both of those on your dashboard and allow the end-user to be able to change them.

                     

                    Create a calculated field, call it InRange? (or whatever the heck you want)

                    The formula is going to be.

                     

                    IF [Rowdate] < StartDate or [Rowdate] > EndDate THEN 0

                    ELSEIF [Rowdate] >= Startdate AND [Rowdate] <= EndDate THEN 1

                    END

                     

                    Put this onto your filters and only allow the number 1.

                     

                    Now only data between the two points are included. I know this is old and  I really have no clue what Anna is asking in the 4th post, I hope this helps Dan.:

                    • 7. Re: Calculated fields on dynamic date range
                      Jose Carreiras

                      Hello Anna,

                       

                      Not sure if you are still looking to do this, but I did it using a calculated field. It is for 2015 and 2016.

                       

                      See below:

                       

                      IF [CONTRACT DATE] >= DATE('12/29/2014') AND [CONTRACT DATE] <= DATE('01/25/2015') THEN 1 //BROADCAST JAN 2015

                      ELSEIF [CONTRACT DATE] >= DATE('12/28/2015') AND [CONTRACT DATE] <= DATE('01/31/2016') THEN 1 //BROADCAST JAN 2016

                      ELSEIF [CONTRACT DATE] >= DATE('01/26/2015') AND [CONTRACT DATE] <= DATE('02/22/2015') THEN 2 //BROADCAST FEB 2015

                      ELSEIF [CONTRACT DATE] >= DATE('02/01/2016') AND [CONTRACT DATE] <= DATE('02/28/2016') THEN 2 //BROADCAST FEB 2016

                      ELSEIF [CONTRACT DATE] >= DATE('02/23/2015') AND [CONTRACT DATE] <= DATE('03/29/2015') THEN 3 //BROADCAST MAR 2015

                      ELSEIF [CONTRACT DATE] >= DATE('02/29/2016') AND [CONTRACT DATE] <= DATE('03/27/2016') THEN 3 //BROADCAST MAR 2016

                      ELSEIF [CONTRACT DATE] >= DATE('03/30/2015') AND [CONTRACT DATE] <= DATE('04/26/2015') THEN 4 //BROADCAST APR 2015

                      ELSEIF [CONTRACT DATE] >= DATE('03/28/2016') AND [CONTRACT DATE] <= DATE('04/24/2016') THEN 4 //BROADCAST APR 2016

                      ELSEIF [CONTRACT DATE] >= DATE('04/27/2015') AND [CONTRACT DATE] <= DATE('05/31/2015') THEN 5 //BROADCAST MAY 2015

                      ELSEIF [CONTRACT DATE] >= DATE('04/25/2016') AND [CONTRACT DATE] <= DATE('05/29/2016') THEN 5 //BROADCAST MAY 2016

                      ELSEIF [CONTRACT DATE] >= DATE('06/01/2015') AND [CONTRACT DATE] <= DATE('06/28/2015') THEN 6 //BROADCAST JUNE 2015

                      ELSEIF [CONTRACT DATE] >= DATE('05/30/2016') AND [CONTRACT DATE] <= DATE('06/26/2016') THEN 6 //BROADCAST JUNE 2016

                      ELSEIF [CONTRACT DATE] >= DATE('06/29/2015') AND [CONTRACT DATE] <= DATE('07/26/2015') THEN 7 //BROADCAST JULY 2015

                      ELSEIF [CONTRACT DATE] >= DATE('06/27/2016') AND [CONTRACT DATE] <= DATE('07/31/2016') THEN 7 //BROADCAST JULY 2016

                      ELSEIF [CONTRACT DATE] >= DATE('07/27/2015') AND [CONTRACT DATE] <= DATE('08/30/2015') THEN 8 //BROADCAST AUG 2015

                      ELSEIF [CONTRACT DATE] >= DATE('08/01/2016') AND [CONTRACT DATE] <= DATE('08/28/2016') THEN 8 //BROADCAST AUG 2016

                      ELSEIF [CONTRACT DATE] >= DATE('08/31/2015') AND [CONTRACT DATE] <= DATE('09/27/2015') THEN 9 //BROADCAST SEPT 2015

                      ELSEIF [CONTRACT DATE] >= DATE('08/29/2016') AND [CONTRACT DATE] <= DATE('09/25/2016') THEN 9 //BROADCAST SEPT 2016

                      ELSEIF [CONTRACT DATE] >= DATE('09/28/2015') AND [CONTRACT DATE] <= DATE('10/25/2015') THEN 10 //BROADCAST OCT 2015

                      ELSEIF [CONTRACT DATE] >= DATE('09/26/2016') AND [CONTRACT DATE] <= DATE('10/30/2016') THEN 10 //BROADCAST OCT 2016

                      ELSEIF [CONTRACT DATE] >= DATE('10/26/2015') AND [CONTRACT DATE] <= DATE('11/29/2015') THEN 11 //BROADCAST NOV 2015

                      ELSEIF [CONTRACT DATE] >= DATE('10/31/2016') AND [CONTRACT DATE] <= DATE('11/27/2016') THEN 11 //BROADCAST NOV 2016

                      ELSEIF [CONTRACT DATE] >= DATE('11/30/2015') AND [CONTRACT DATE] <= DATE('12/27/2015') THEN 12 //BROADCAST DEC 2015

                      ELSEIF [CONTRACT DATE] >= DATE('11/28/2016') AND [CONTRACT DATE] <= DATE('12/25/2016') THEN 12 //BROADCAST DEC 2016

                      END

                       

                       

                       

                      I also created it for the quarter:

                       

                      IF [CONTRACT DATE] >= DATE('12/29/2014') AND [CONTRACT DATE] <= DATE('03/29/2015') THEN 1 //BROADCAST Q1 2015

                      ELSEIF [CONTRACT DATE] >= DATE('12/28/2015') AND [CONTRACT DATE] <= DATE('03/27/2016') THEN 1 //BROADCAST Q1 2016

                      ELSEIF [CONTRACT DATE] >= DATE('03/30/2015') AND [CONTRACT DATE] <= DATE('06/28/2015') THEN 2 //BROADCAST Q2 2015

                      ELSEIF [CONTRACT DATE] >= DATE('03/28/2016') AND [CONTRACT DATE] <= DATE('06/26/2016') THEN 2 //BROADCAST Q2 2016

                      ELSEIF [CONTRACT DATE] >= DATE('06/29/2015') AND [CONTRACT DATE] <= DATE('09/27/2015') THEN 3 //BROADCAST Q3 2015

                      ELSEIF [CONTRACT DATE] >= DATE('06/27/2016') AND [CONTRACT DATE] <= DATE('09/25/2016') THEN 3 //BROADCAST Q3 2016

                      ELSEIF [CONTRACT DATE] >= DATE('09/28/2015') AND [CONTRACT DATE] <= DATE('12/27/2015') THEN 4 //BROADCAST Q4 2015

                      ELSEIF [CONTRACT DATE] >= DATE('09/26/2016') AND [CONTRACT DATE] <= DATE('12/25/2016') THEN 4 //BROADCAST Q4 2016

                      END

                      1 of 1 people found this helpful