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

# Calculation based on Date Range Selection

Hi-

I have data in the following format:

 BU State Headcount Year Number of Customers A AZ 680 01/01/2007 44 B AZ 10 01/01/2007 55 PI AZ 20 01/01/2007 899999 C AZ 30 01/01/2007 55 A AZ 680 01/01/2010 22 B AZ 880 01/01/2010 411 C AZ 150 01/01/2010 228768

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).

Ralph

• ###### 1. Re: Calculation based on Date Range Selection

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

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

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