Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
UserID2626
Creator III
Creator III

MOM column in Pivot table Qlik Sense

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):
Capture.PNG

Please help me to get correct result for MOM column(difference of May&April.)

 

Thanks in advance 🙂

 

Labels (2)
1 Solution

Accepted Solutions
kaanerisen
Creator III
Creator III

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

Untitled.png

View solution in original post

1 Reply
kaanerisen
Creator III
Creator III

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

Untitled.png