9 Replies Latest reply: Jul 18, 2013 7:10 AM by Azam Mullick RSS

    How to show current and prior period values in one row? and able to drill down?

      At first, my requirment is to present current and prior period booking data of hotel and the city where hotel belong to in one row.

      I create a pivot table like table 1, Hotel Name and City Name are dimensions, and others are experssions.

       

      Table1

      Table1.png

      My data model looks like image 1:

      * A index table, provide fields to find hotels

      * A hotel level and a city level aggregation table, both linked with index table

      * A YearMonth table to select current period(one or more month), apply field OnSelect trigger to auto select YearMonth PY table when YearMonth is selected

       

      Image 1

      Image1.png

      Here is definition of experssions:

      Hotel Booking CY = sum({$<[YearMonth Hotel]=[YearMonth]>}[Hotel Bookings])

      Hotel Booking PY = sum({$<[YearMonth Hotel]=[YearMonth PY]>}[Hotel Bookings])

      City Booking CY = sum({$<[YearMonth City]=[YearMonth]>}[Hotel Bookings])

      City Booking PY = sum({$<[YearMonth City]=[YearMonth PY]>}[Hotel Bookings])

       

      and search string of the YearMonth trigger:

      ='('&concat(distinct date(AddMonths([YearMonth],-12),'MMM-YYYY'),'|')&')'

       

      All of above works fine, but a littel change of requirment.

       

      they need both table 1 and drill down table 1 through YearMonth, like table 2. It doesn't work if I just add YearMonth in the table, values in each row are summary of entire period, not break down by YearMonth.

       

      Table2

      Table2.png

       

      Please help me solve the problem, many thanks! 

       

      P.S.  Sorry for my terrible english.