MOM column in Pivot table Qlik Sense

[UserID2626]

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 🙂

[kaanerisen · Accepted solution]

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))