3 Replies Latest reply on Oct 16, 2017 11:13 AM by Phil Heinle

    Compare a Selected date to a Previous Period

    Phil Heinle

      Hello Everyone,


      I am looking for some help regarding the use of date functions, date parameters and calculated fields to return my desired output. My goal is to create a view that allows me to select a reference date, ideally the most recent day of data from the data source, and compare it to a previous period, the average of the previous week/month. I have completed a way to view month to month data but I want to take it a step further by using parameters to adjust the view. I have attached a workbook that has my most recent attempt at this. On the last three sheets I have set up parameters to compare a selected period to the previous period but I would much rather have control over the reference date in order to compare it to the desired previous period. In my workbook, I am only able to compare similar period sizes against each other instead of having a single or various reference date that is different from the previous period.


      I have been unsuccessful in attempting to compare the max date in the data source to the average of the most previous week. I tried setting up various date dimensions but when they are brought down to the 'week' level, they are unable to used in table calculations.


      I believe the answer lies within the combination of the correct parameters, date functions and table calculations. If anyone has any experience with this and has any suggestions to help create my desired view that would be greatly appreciated.


      Again, my goal is to create a view that allows me to compare a reference date to previous periods that include previous weeks and months.


      Thank you.

        • 1. Re: Compare a Selected date to a Previous Period
          Joe Oppelt

          Lots of possible moving parts in this.


          I have a workbook that does something similar.


          In the attached I added some things.  First I gave you a Max Date LOD calc that will always tell you the max date in your data source.


          Also, I have a parameter that lets the user select what he wants to do.


          So, for instance, in my workbook if the user selects the latest date, then I work off the Max Date calc.  But if he wants a specific date, then I pop out the [Reference Date] parameter to let him select an earlier date.  Once you have your starting date, you can do all your calcs based on that date.


          So that's the first step.


          Decide if that's useful to you, and we can move forward from there.

          • 2. Re: Compare a Selected date to a Previous Period
            Joe Oppelt

            If you're not familiar with popping out parameters, see the attached.


            In Dashboard 1 I added one of the sheets.  I just positioned stuff in random places.  Go to Dashboard 2.  First of all I made a new sheet that will display (or not) based on the value of [What do I want to see].  See sheet "Pop out Reference Date".


            On this sheet I added a bogus dimension that I created.  (You need a dimension of some sort on the sheet to force a sheet to disappear when the condition dictates.)  Play with the {what do I want to see] parameter when you are on that sheet.


            Go back to Dashboard 2.  Play with the same parameter there.  Do you see how the [Reference Date] parameter pops up and down?  That's popping.


            I have created a bogus text box floating behind [What do I want to see] parameter on the dashboard.  I colored the text white to match the background, and I formatted the box to be white so that it is opaque.  We're going to place the popping container behind this so that we don't see the pop-out sheet doing its work.


            Click on "Layout" on Dashboard2. Select "Specific Date" in "What do I want to see] so that the pop-out is expanded.  Then drag the pop-out container so that it is positioned over the [What do I want to see] parameter so that the [Reference Date] parameter is positioned below [What do I want to see].  Right click on the vertical container itself (either on the dashboard itself, or in the object list) and select floating order.  The select "Send to back".  Now it is floating under the opaque text object (which is under the [What do I want to see] parameter) and when you select "Specific Date" the date parameter pops into view, and when you select "Max Date} it hides behind the opaque box, out of view as you see on Dashboard 3.

            • 3. Re: Compare a Selected date to a Previous Period
              Phil Heinle



              Great advice for the max date LOD along with the reference date parameter. I ended up combining the two parameters within a case function that just ignores the reference date if the max date in data is chosen.


              I think I may need to either adjust or completely overhaul my Rolling Period table calc to achieve my desired result. This is the point in which I have had the most trouble combining the correct functions to show the view of comparing my reference point to various types of previous periods.


              In your opinion, would it be more realistic to just have my dashboard ask how many previous days to compare to the most recent data point?