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

    how to display data with overlapping dates

    Thomas Beaulieu

      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:

      Capture3.PNG.png

       

       

      Here is what I am getting

       

      Capture2.PNG.png