topic This Month values minus Last month values in script in QlikView

This Month values minus Last month values in script

HermGerm — Thu, 26 Mar 2020 13:25:50 GMT

Hi,

in a load script, I want to calculate the difference between "this month" sales and "last month" sales, where the "Elementname" is the same.

Tables looks like this:

Data:

Elementname,

Sales,

Date

So written in words something like this:

newData:

LOAD

Elementname,

Sales,

Date,

Sum("where Elementname = Elementname and date = date") - Sum("where Elementname = Elementname and Date=Addmonth(date,-1)") as "prSales"

Example data could be:

AB,100,01.01.2020

AB,50,01.12.2019,

AC,100,01.02.2020

Expected is this:

100-50 (for AB)

100-0 (for AC, since there is no value from previous month)

How to do this?

Re: This Month values minus Last month values in script

Saravanan_Desingh — Sun, 19 Apr 2020 01:16:23 GMT

Are you looking like this?

tab1: LOAD *, MonthStart(Date#(Sales_Date,'DD.MM.YYYY')) As Sales_MS INLINE [ Elementname, Sales, Sales_Date AB, 100, 01.01.2020 AB, 50, 01.12.2019 AC, 100, 01.02.2020 ]; tab2: LOAD Elementname, Sales_MS, Sum(Sales) As Sales_Sum Resident tab1 Group By Elementname, Sales_MS ; Left Join (tab2) LOAD Elementname, Count(Sales_MS) As MS_Count Resident tab1 Group By Elementname ; tab3: LOAD Elementname, Sales_MS, If(MS_Count=1,Sales_Sum,If(Elementname=Peek(Elementname),Sales_Sum - Alt(Peek(Sales_Sum),0))) As Sales_Diff, Sales_Sum , Peek(Sales_Sum) As Sales_Peek Resident tab2 Order By Elementname, Sales_MS Asc ; Drop Table tab1, tab2;