Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I need to create a Pivot table and need to add an column called MOM in end of the table, which is difference between current month-previous month.
It is not possible in dashboard, so by using script created to different tables and concatenated with common column. but i am not getting correct MOM result.
SET DateFormat='DD/MM/YYYY';
Sample_Table:
load *
Inline [
Sale_Date,Value,Product
'01/01/2018',50,A
'10/01/2018',60,B
'01/02/2018',213,B
'21/02/2018',11,A
'21/02/2018',2,A
'03/03/2018',370,B
'01/03/2018',50,A
'10/03/2018',60,B
'01/04/2018',23,B
'21/04/2018',24,A
'03/05/2018',37,B
'06/05/2018',3,A
'01/05/2018',12,A
];
//grouping values by month
Sum_table:
load
Product as Sum_Product,
Month(Sale_Date) as Sum_Month,
Sum(Value) as Sum_Value
resident Sample_Table group by Month(Sale_Date),Product
;
//table to get MOM
MOM_Table:
Load
Sum_Product as MOM_Product,
Sum_Month as MOM_Month,
Sum_Value as MOM_Sum,
Sum_Value-Previous(Sum_Value) as pre_Month_Diff
resident Sum_table;
//concatenation for pivot table
final:
Load
Sum_Month as 'Month',
Sum_Product as 'Product_1',
Sum_Value as 'Value_1'
resident Sum_table ;
Concatenate
load
'MOM' as 'Month',
MOM_Product as 'Product_1',
pre_Month_Diff as 'Value_1'
Resident MOM_Table ;
drop table MOM_Table;
Drop table Sum_table;
I am getting below image as result(MOM result is incorrect):
Please help me to get correct result for MOM column(difference of May&April.)
Thanks in advance 🙂
Hi vivethakumar,
How about this?
Script:
Sample_Table:
load *,num(Month(Sale_Date)) as MonthNo
Inline [
Sale_Date,Value,Product
'01/01/2018',50,A
'10/01/2018',60,B
'01/02/2018',213,B
'21/02/2018',11,A
'21/02/2018',2,A
'03/03/2018',370,B
'01/03/2018',50,A
'10/03/2018',60,B
'01/04/2018',23,B
'21/04/2018',24,A
'03/05/2018',37,B
'06/05/2018',3,A
'01/05/2018',12,A
];
ColumnTemp:
load distinct MonthNo,Product,Month(Sale_Date) as Month Resident Sample_Table;
Concatenate
load distinct MonthNo,Product,'MOM' as Month Resident Sample_Table;
Row Dimension: Product
Column Dimension: Month (Column Sorting is "Custom" and uncheck all options.)
Mesaure : IF(Month='MOM',Before(SUM(Value))-Before(SUM(Value),2),Sum(Value))
Hi vivethakumar,
How about this?
Script:
Sample_Table:
load *,num(Month(Sale_Date)) as MonthNo
Inline [
Sale_Date,Value,Product
'01/01/2018',50,A
'10/01/2018',60,B
'01/02/2018',213,B
'21/02/2018',11,A
'21/02/2018',2,A
'03/03/2018',370,B
'01/03/2018',50,A
'10/03/2018',60,B
'01/04/2018',23,B
'21/04/2018',24,A
'03/05/2018',37,B
'06/05/2018',3,A
'01/05/2018',12,A
];
ColumnTemp:
load distinct MonthNo,Product,Month(Sale_Date) as Month Resident Sample_Table;
Concatenate
load distinct MonthNo,Product,'MOM' as Month Resident Sample_Table;
Row Dimension: Product
Column Dimension: Month (Column Sorting is "Custom" and uncheck all options.)
Mesaure : IF(Month='MOM',Before(SUM(Value))-Before(SUM(Value),2),Sum(Value))