3 Replies Latest reply: Apr 4, 2015 4:06 AM by Bhagirath Shingote RSS

    Multiple calendar and aggregation problem

    Alexis Hadjisoteriou

      Hi all,

       

      I am sure this is a common problem - let me explain. In the diagram below you can see that I am using 3 separate calendars one each for Deliveries, Orders and Shipments.

       

      MultipleCalendars.jpg

      In the "blue" table (below) I am combining "Orders" (from the Order Calendar) and "Deliveries" from the "Delivery Calendar" (both shown in yellow) using the Dimension "Month Year" from an unconnected calendar called "Calendar" (shown in pink above) and then using syntax of the type:

      aggr(

          if(MonthYear = [Order MonthYear],

              sum(Orders.Counter)

          ) ,MonthYear ,[Order MonthYear]) for "Orders" and


      aggr(

          if(MonthYear = [Delivery MonthYear],

              sum(Orders.Counter)

          ) ,MonthYear ,[Delivery MonthYear]) for "Deliveries"

       

      MultipleCalendarsExample.jpg

      So far so good - all works fine and the numbers are correct.

      Choosing a "Country" from the list box yields the correct results in the blue table.

       

      My requirement, however, is to extend the "blue" table to show Orders and Deliveries for each country for every month (see pink table above). The resultant pivot table is incorrect - any help would be most appreciated. I attach the above demo application with data.

       

      Best regards

      Alexis