3 Replies Latest reply on Aug 30, 2012 1:24 PM by Ralph Lindenfeld

    Calculation based on Date Range Selection

    Ralph Lindenfeld

      Hi-

       

      I have data in the following format:

      BUStateHeadcountYearNumber of Customers
      AAZ68001/01/200744
      BAZ1001/01/200755
      PIAZ2001/01/2007899999
      CAZ3001/01/200755
      AAZ68001/01/201022
      BAZ88001/01/2010411
      CAZ15001/01/2010228768

       

      I have a 'Range of Dates' Filter Field that the user will be able to select a range of dates (the date range is more than just 2007 and 2010). Based on that selection, I wish to perform a calculation of difference in customers per BU, per State from the earliest to the latest selection in the range. For example, if the user selects 2007-2010, the calculation should reflect the difference between 2007 and 2010. If they select 2008 and 2010, it would calculate the change between 2008 and 2010 (data for 2008 not shown above).

       

      Thanks for your help!

       

      Ralph

        • 1. Re: Calculation based on Date Range Selection
          Mark Holtz

          One way to accomplish this would be to create 2 date parameter fields: [Start Date] & [End Date]

          (Since you're doing years, instead of doing Dates, you could also make each parameter an Integer list between 2007 and 2010, or whatever your year range is.)

           

          Then, create 2 calculated fields:

          Start Number of Customers...

          IF [Start Date] = [Year]

          // if you are using Integer Years instead, then use DATEPART('year',[Year]) instead of [Year]

          THEN [Number of Customers]

          ELSE NULL

          END

           

          End Number of Customers...

          IF [End Date] = [Year]

          // if you are using Integer Years instead, then use DATEPART('year',[Year]) instead of [Year]

          THEN [Number of Customers]

          ELSE NULL

          END

           

          Then create one more calculated field as [Difference]:

          SUM([Start Number of Customers]) - SUM([End Number of Customers])

           

          When you place [Difference] on a view, you can add the [State] and [BU] fields to slice by.

          Does that help?

           

          (Another--slicker--way that might work is to make [MaxDate] and [MinDate] fields as: TOTAL(MAX([YEAR])) and TOTAL(MIN([Year])). Then, use those in the 2 calculated fields instead of the parameters in [Start Number of Customers] and [End Number of Customers])

          1 of 1 people found this helpful
          • 2. Re: Calculation based on Date Range Selection
            Ralph Lindenfeld

            That's exactly what I tried, and got an error on the difference calc. I think Steph is answering this as part of thread creep on a map posting. Sorry for double posting. However, as I have a mock-up due tomorrow, I'm only sort of sorry

            • 3. Re: Calculation based on Date Range Selection
              Ralph Lindenfeld

              Hi Mark-

               

              So, I tried to use the Max-and-MinDates as you suggested, and as a first test I put MaxDate in in the Label of Population End worksheet. However, it does not seem to be affected by the End Date parameter.

               

              What am I doing wrong?

               

              Thanks,

               

              Ralph