3 Replies Latest reply on May 10, 2012 5:26 AM by davidclaen

    First and Last Date from Range as Columns

    davidclaen

      Hi,

       

      I have a quickfilter for dates as a range.

      Now I want the first and last day of the defined range as columns in my view.

      I created two calculated fields, one with min(date) and one with max(date). When I drag this fields to the columns I got the two dates one below the other. But I want the fields side by side.

       

      How could I achieve this?

      David

        • 1. Re: First and Last Date from Range as Columns
          Tracy Rodgers

          Hi David,

           

          This can be done by creating a calculated field similar to the following:

           

          if attr([Order Date])=total(max([Order Date])) then 1

          elseif attr([Order Date])=total(min([Order Date])) then 1

          else 0

          end

           

          Place the date field on the Columns shelf and and the above calculation on the rows shelf. Make sure the calculation is set to Discrete. Then, right click on it and select Compute Using-->Table (Across). Right click on it and select Filter. Select only 1. If desired, right click on the calculation again and un-check Show Header.

           

          Hope this helps!

           

          -Tracy

          • 2. Re: First and Last Date from Range as Columns
            Dennis Schoder

            Hello David,

             

            You could apply the 'date' field once more as a filter. Drag it to the 'Filters' shelf again and select 'Individual dates and times', hit 'Next', and tick 'Use all'. Then show this newly created Filter as a 'quick filter', when it appears on the right-hand side, open its menu and select 'only relevant values'. This list only shows you the dates from the date range filter, and you can easily select the first and last date. The two days should be displayed in the format you wanted. (the date field needs to be on the columns shelf for that as well)

             

            Maybe this works as well for you,

            Dennis

            1 of 1 people found this helpful
            • 3. Re: First and Last Date from Range as Columns
              davidclaen

              Thank you very much for your help.

              That works exactly how I expact. But now I have the next challenge. I want to calculate the difference between the measure of the last date and the first date.

              My approach was to create a calculated field with: LOOKUP(sum([DailyPageEngagedUsers]), last() ) - LOOKUP(sum([DailyPageEngagedUsers]), first() ).

              It calculates the difference but it comes up twice if I drag it to the measurebox, one time for each date.

              Do you have one further hint for me?

               

              Thanks,

              David

               

              EDIT:

              One more Point: If i display the sum of the columns i got all dates back ...