3 Replies Latest reply on Sep 2, 2018 5:27 PM by Sam Beyer

    Current period vs prior period with multiple date types

    Mark Palmberg

      I used this post by Olga Tsubiks to create the attached packaged workbook. It works great (thanks, Olga!), but I'd like to add some complexity to it, and I'm not sure how. (Similar stuff going on in this post, which Joe Oppelt answered.)

       

      Each of the rows in my data has one of three states: Expected; Ask, or Gift. All should have an Expected Ask Date. If the Ask Date is non-null, then it's an Ask (tied to the Ask Date); if the Response Date is non-null and the status is 'Accepted,' it's a Gift (tied to that response date). The "gift" is only identified as such via the 'Accepted' status, so I created a field called 'gift_date'. Right now, the attached workbook is using only the expected ask date in Current Period and Comparison Period calculated fields.

       

      In Olga's original example, she's using Superstore data and only profit, quantity, and sales based on the order date. I'd like to give my end user the ability to see results based on either Expected Date, Ask Date, or Response Date (and their related amounts), but I'm not sure how best to go about it. The opportunity_count will still work fine no matter which date is used, but the expected, ask, and gift amount can be -- and often are -- different.

       

      Thanks so much for any time you can spare on this. It's much appreciated!

        • 1. Re: Current period vs prior period with multiple date types
          Sam Beyer

          Hi Mark,

           

          If I understand what you're trying to do correctly, you can create a parameter (State to Show) which can take on the values "Expected", "Ask" or "Gift" and then create a calculated field which takes it's value from the correct date column based on the parameter:

           

          IF [State To Show] == "Gift"

              THEN [gift_date]

          ELSEIF [State To Show] == "Ask"

              THEN [ask_date]

          ELSEIF [State To Show] == "Expected"

              THEN [expected_ask_date]

          ELSE NULL

          END

           

          Once you have this, replace the date you're using (you'll need to edit the table calculation too to avoid errors) and show the parameter control on the side. I also added a calculated field to help filter out rows without a date.

           

          Let me know how you go!

           

          Thanks,

          Sam.

          1 of 1 people found this helpful
          • 2. Re: Current period vs prior period with multiple date types
            Mark Palmberg

            Thanks so much for this reply, Sam. I have to apologize for not being clearer in my original question (and for including an extra sheet in my twbx). I'm primarily interested in getting the "Current v. prior" sheet to work, which is a bit more involved than the sheet that had the table calc in it.

             

            It seems to me that your suggestion of the "State to Show" param is the path to follow, but I'm still in knots trying to figure how to wrap that parameter value around my calculated fields. E.g., the Comparison Period calc:

             

            IF

            DATETRUNC([Compare by], [expected_ask_date]) =

            DATETRUNC([Compare by], DATEADD([Compare by], -IIF([Compare to]=1,1,IIF([Compare to]=2 AND [Compare by]="day",365,IIF([Compare to]=2 AND [Compare by]="month",12,IIF([Compare to]=2 AND [Compare by]="quarter",4,IIF([Compare to]=2 AND [Compare by]="year",2,0))))),[Start Date]))

            AND

            DATETRUNC('day', [expected_ask_date]) <=

            DATETRUNC('day', DATEADD([Compare by], -IIF([Compare to]=1,1,IIF([Compare to]=2 AND [Compare by]="day",365,IIF([Compare to]=2 AND [Compare by]="month",12,IIF([Compare to]=2 AND [Compare by]="quarter",4,IIF([Compare to]=2 AND [Compare by]="year",2,0))))),[Start Date]))

            THEN

            [Select a metric]

            END

             

            Do I wrap this in an IF/ESLEIF that references the State to Show parameter? And then, of course, you'd *have to* select the metric that aligns with the State to Show value you've selected in order to get correct results.

             

            Thanks again for taking a look at this, Sam. I appreciate your willingness to help out!

            • 3. Re: Current period vs prior period with multiple date types
              Sam Beyer

              Hi Mark,

               

              Sorry for the misunderstanding. I think what I've done will still work.

               

              I added a 'State To Show' parameter and a calculated field 'Date'. The value in 'Date' will change based on the value of 'State To Show' (you might want to check the calculation for 'Date', but hopefully you can see what I'm trying to do).

               

              The idea is that instead of using the 'expected_ask_date' in your calculations, you can use 'Date' and the value in 'Date' will change depending on the value of 'State To Show'.

               

              I've made the changes I think you'll need (just changing [expected_ask_date] to [Date] in the 'Comparison Period' calculation) to help guide you Let me know how you go!

               

              Thanks,

              Sam.