2 Replies Latest reply on Jul 3, 2013 1:18 PM by Thomas Beaulieu

# how to display data with overlapping dates

Hello - I have sales values from Jan 1, 2011 through June 30, 2013.  There are about 50,000 lines of excel data comprising these sales figures.  I would like to display sales totals according to the following ranges -

• 2011 calendar year
• 2012 calendar year
• July 1 2012 through June 30 2013
• 2013 ytd and then double this value to show the annualized sales

I tried the following calculated field code:

if[Book Year] = 2011 then "2011"

elseif

[Book Year] = 2012 then "2012"

elseif

[Book Date] >= #7/1/2012# then "12 mo. ending June 30, 2013"

elseif

[Book Year] = 2013 then "1H2013"

end

The problem is that the [Book Year] = 2012 then "2012" takes all of the data for 2012 and leaves no 2012 data for the [Book Date] >= #7/1/2012# then "12 mo. ending June 30, 2013" calculation.  The [Book Date] >= #7/1/2012# then "12 mo. ending June 30, 2013"  calculation ends up displaying sales for just the first 6 mo. of 2013.  The final calculation, [Book Year] = 2013 then "1H2013", shows nothing since all 2013 data was used up by the prior calculation.  Also, I have no idea how to double the sales value of just 2013 sales.  Any advice?

Here is what I want:

Here is what I am getting

• ###### 1. Re: how to display data with overlapping dates

You will need to create 4 separate measures:

2011:

SUM(if [Book Year] = 2011 then [Sales] END)

2012

SUM(IF [Book Year]=2012 THEN [Sales] END)

etc.

Then you can create a bar chart with Measure Names on Columns, and Measure Values on rows, with the 4 measures being selected.

• ###### 2. Re: how to display data with overlapping dates

Worked perfectly Jonathan.  Thanks for taking the time to help me!

Tom B.