1 Reply Latest reply on Oct 17, 2016 7:42 AM by Jeremy Blaney

    Calculate differences in values between fixed/user specified dates

    Jeremy Blaney

      Hi all,

       

      I'm new to parameters - hoping someone can point me in the right direction.

       

      I need to create a viz that calculates the delta, percent diff., etc. between the most recent month and a user-specified previous month. Here's what my data looks like:

       

      ManagerMovement TypeHeadcount
      Report Date
      Person 1Movement Type 11509/30/2016
      Person 1Movement Type 2509/30/2016
      Person 2Movement Type 11009/30/2016
      Person 2Movement Type 2759/30/2016
      Person 1Movement Type 11406/30/2016
      Person 1Movement Type 2506/30/2016
      Person 2Movement Type 1956/30/2016
      Person 2Movement Type 2746/30/2016
      Person 1Movement Type 11103/31/2016
      Person 1Movement Type 2803/31/2016
      Person 2Movement Type 11053/31/2016
      Person 2Movement Type 2803/31/2016

       

      In other words, the current month (baseline) will always default to 9/30 (or whatever the most recent month is). The compare month needs to be user specified (i.e., they can select 6/30, 3/31, etc.).

       

      I know I need to use parameters, but I'm complete new to that game. I also won't have a problem create trend arrows and all that.

       

      Just need help getting started! Any help would be much appreciated!

        • 1. Re: Calculate differences in values between fixed/user specified dates
          Jeremy Blaney

          Here's where I'm currently at...

           

          Most Recent Report

          I have my most recent report identified using the following calculated field (Most Recent Report Date):

               [Report Date] = { FIXED : MAX([Report Date]) }

           

          All Other Reports

          The most recent report is separated from all other reports using the following calculated field (Compare Report Date):

               IF [Report Date] = { FIXED : MAX([Report Date]) } THEN NULL

               ELSE [Report Date]

               END

           

          I can put these two dimensions on my columns and use a table count to show the difference. Yay!

           

          But it's the next step that's giving me trouble... With both dimensions on the columns, I have three options (all three dates).

           

           

          I tried adding Compare Report Date to the filters to get rid of either Mar 31 or June 30. It works, but I have to leave "Null" enabled the quick filter, which is less than ideal.

           

           

          I'm fairly certain a parameter will work here, but not sure where it comes into play.