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

[#1]

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)

[sunny_talwar]

Are you looking for this:

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

[sunny_talwar]

Or you can use a pivot table:

Pivot table uses two dimensions (one is calculated)

1)=If(Len(Trim(Aggr(Sum({<MonthYear = {"$(=MonthName(Max(MonthYear)))"}>}Sales), [Product Name]))) > 0, [Product Name])

2) MonthYear

and 1 expression

=Sum({<MonthYear = {"=Num(MonthYear) >= Num(MonthName(AddMonths(Date(Max(MonthYear)), -2)))"}>}Sales)

Best,

Sunny

[ramoncova06]

here is another option