Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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
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
here is another option