3 Replies Latest reply: Jun 29, 2015 3:43 PM by Ramon Covarrubias RSS

    Re: Last three months sales for current month product id and product type

      Hi Team,

              Product ID   ,Product Name,  Product Type, MonthYear,Sales

                  1               , Dairy Milk       , Chocolate     , 062015     , 400

                  2               , Abcd Milk       , Chocolate     , 062015     , 300

                  3               , Xyzv Milk       , Chocolate     , 062015     , 600

                  1               , Dairy Milk       , Chocolate     , 052015     , 400

                  2               , Abcd Milk       , Chocolate     , 052015     , 300

                  3               , Xyzv Milk       , Chocolate     , 052015     , 600

                  1               , Dairy Milk       , Chocolate     , 042015     , 400

                  2               , Abcd Milk       , Chocolate     , 042015     , 300

                  3               , Xyzv Milk       , Chocolate     , 042015     , 600

                  4               ,Aaaa Milk        ,Chocolate     , 042015      ,700

       

      Please consider above example data ,

      1.I want to show Last three month data in different columns for only which  products are sold on current month(example :Product ID "4" I don't want to include)

        • Re: Last three months sales for current month product id and product type
          Sunny Talwar

          Are you looking for this:

           

          Capture.PNG

           

          Script:

           

          Table:

          LOAD [Product ID],

            [Product Name],

            [Product Type],

            MonthName(Date#(MonthYear, 'MMYYYY')) AS MonthYear,

            Sales;

          LOAD * Inline [

          Product ID  ,Product Name,  Product Type, MonthYear,Sales

                      1              , Dairy Milk      , Chocolate    , 062015    , 400

                      2              , Abcd Milk      , Chocolate    , 062015    , 300

                      3              , Xyzv Milk      , Chocolate    , 062015    , 600

                      1              , Dairy Milk      , Chocolate    , 052015    , 400

                      2              , Abcd Milk      , Chocolate    , 052015    , 300

                      3              , Xyzv Milk      , Chocolate    , 052015    , 600

                      1              , Dairy Milk      , Chocolate    , 042015    , 400

                      2              , Abcd Milk      , Chocolate    , 042015    , 300

                      3              , Xyzv Milk      , Chocolate    , 042015    , 600

                      4              ,Aaaa Milk        ,Chocolate    , 042015      ,700

          ];

           

          Straight Table with Product Name as the dimension and three expressions:

           

          1)=Sum({<MonthYear = {"$(=MonthName(Max(MonthYear)))"}>}Sales)

          2)=If(Column(1) > 0, Sum({<MonthYear = {"$(=MonthName(AddMonths(Date(Max(MonthYear)), -1)))"}>}Sales))

          3)=If(Column(1) > 0, Sum({<MonthYear = {"$(=MonthName(AddMonths(Date(Max(MonthYear)), -2)))"}>}Sales))

           

          HTH

           

          Best,

          Sunny