2 Replies Latest reply on Dec 7, 2012 8:42 AM by Catherine Rivier

    Pace showing room nights sold by arriving date as of last year same month

    Miriam Perkins

      We're trying to create a pace report of hotel room nights sold by month comparing with same month last year. For example: All room night arrivals as of 11/12 and all room night arrivals as of 11/11, both for following year.What would be the formula (s)?   Please help!

        • 1. Re: Pace showing room nights sold by arriving date as of last year same month
          Alex Kerin

          Here's one way - I created a calculation that stripped out the year. We can then use this with a running sum to and the year part to compare years.

          • 2. Re: Pace showing room nights sold by arriving date as of last year same month
            Catherine Rivier

            Hi!  Alex's solution is ideal, I think.

             

            But in the spirit of multiple options, here's another possibility.  More difficult to maintain, and since it uses linked data sources, can have more issues.  But it does do literally what you're looking for above.

             

            Basically, you can make a duplicate of your data source, and link it back to itself based on the previous year's month. See the attached workbook for how it works.  Here are the steps:

             

            1. Create calculated field (which extracts the Month Year of the date), called Meet Begin Month: DATETRUNC('month',[Meet Begin])
            2. And the value for the previous year, same month, called Meet Begin Month - PreviousYear:  DATE(DATETRUNC('month',DATEADD('year',-1,[Meet Begin])))

             

            Now to link the two data sources cleanly, I like to create a LINK calculated field with the same name in both sources, so it will naturally link.  In this case, it's field LINKMONTH, which is the previous year month in the Primary data source, and the current year month in the Secondary.

             

            Finally, the Final View sheet shows (I think) exactly what you describe - the month and its attendance, it's previous year's month and its attendance.

             

            But as I said, this is more difficult to maintain, but it is an alternate solution!