Previous month sales in loading script

[razvan_brais]

Hy guys. Hope you can help me. 

 I need to load from script in the same table current month sales , but I need to load previous month sales too.

 The script looks something like this : 
 

LOAD

CLIENT_ID,
CLIENT_NAME,
LOCATION_ID,
ApplyMap('MapLocations', Month, 'no_location') AS LOCATION_NAME,
SEGMENT,
BUSSINES_FLG,
NPL,
RATING,
MakeDate([YEAR], [Month]) AS HIST_DATE,
NUM([Month]) AS No_Month,
Month(MakeDate(2000,NUM([Month]), 1)) AS MonthMonth,
SALES,
Previous(SALES) as PREV_SALES

FROM TABLE;

The problem is when I`m comparing current sales for January with previous month December... 

I don`t want to do this in SetAnalysis  , I would like to have them calculated from the loading script.

Can you please help me with some hints?

Thank you,

Razvan

[ArnadoSandoval]

@razvan_brais 

Did you reply to my latest suggestion or to Vegar?

Anyhow, with my script, we created several PREV_ columns to produce the PREV_SALES, we do not need them anymore, we just drop them from the table, these are the statements to drop these fields:

Drop Field PREV_CLIENT_ID   from DATA;
Drop Field PREV_HIST_DATE   from DATA;
Drop Field PREV_LOCATION_ID from DATA;
Drop Field PREV_SEGMENT     from DATA;
Drop Field PREV_YEAR        from DATA;
Drop Field PREV_NO_MONTH    from DATA;
Drop Field Row_No           from DATA;
Drop Field Record_No        from DATA;

We keep the PREV_SALES column, after this cleansing the size of the results QVD is significantly smaller, as shown below:

The new QVD with the new PREV_SALES column increased by 1.55 times its original size.

Hope this helps, 

[HirisH_V7]

This code may work your case..

Temp_Load:
LOAD CLIENT_ID, 
     LOCATION_ID, 
     SEGMENT, 
     Date(HIST_DATE,'DD-MMM-YYYY') as HIST_DATE, 
     No_Month, 
     SALES
FROM
[SampleData.xlsx]
(ooxml, embedded labels, table is Sheet1);

Max_Month:
Load Max(HIST_DATE) as Max_Date
Resident Temp_Load;

Let vMaxMonth=Peek('Max_Date');
Drop Table Max_Month;

Final:
Load CLIENT_ID, 
     LOCATION_ID, 
     SEGMENT, 
     HIST_DATE, 
     No_Month, 
     //CurrentMonth,PreviousMonth,
If(CurrentMonth='T',Sum(SALES)) as Current_Month_Sales,
If(PreviousMonth='T',Sum(SALES)) as Previous_Month_Sales Group by 
 CLIENT_ID, 
     LOCATION_ID, 
     SEGMENT, 
     HIST_DATE, 
     No_Month,CurrentMonth,PreviousMonth;
Load *,
  If(InMonth(HIST_DATE, $(vMaxMonth), 0),'T','F') as CurrentMonth,//Just check how date is evaluating in your env or include date prefix
    If(InMonth(HIST_DATE, $(vMaxMonth), -1),'T','F') as PreviousMonth//Just check how date is evaluating in your env  or include date prefix
Resident  Temp_Load;
Drop Table Temp_Load;

I did wrote above code for achieving current and previous month sales alone, if i m missing any or some exclusions need to be in place means let me know. That you can put in front of any of expressions  with sum of sales.

[razvan_brais]

@HirisH_V7 ,

 Unfortunately the script isn`t producing de wanted result. It doesn`t calculate the previous month sales. 

 I would like to show in an kpi object ( text object) the total sales for current selection and in another object total sales of previous month.

Thank you

[razvan_brais]

@ArnadoSandoval  , thank you for your time.

 Regarding your questions. 

  "what is the expected behaviour if you select the 15/01/2020" - there won`t be selection for 15/01/2020 because sales are monthly based( we can consider as date Jan 2020 , Dec 2019 etc. ) 

"My conclusion thus far is that the SUM of SALES will never match with the SUM of PREV_SALES" - this is absolutely corret.

  Lets say I have the next values calculated from the script for each month: 

   FEB 2020 - 1000

   JAN 2020 - 2500

   DEC 2019 - 5000

What I want to achive in the UI is the next behavior : 

  When I select FEB 2020 I will get the result for SALES : 1000 and the result for PREV SALES : 2500 ( value of JAN 2020 SALES);

When I select JAN 2020 I will get the result for SALES : 2500 and the result for PREV SALES : 5000 ( value of DEC 2019 SALES)

What I observed from the current script is that if I select HIST_DATE as JAN 2020:

 In JAN 2020 I have sales for only 4 clients and in DEC 2019 I have sales for 6 clients - the PREV_SALES is being calculated only for the 4 clients that have sales in JAN 2020 , the other 2 clients from DEC 2019 are excluded. And that`s why there are differences between values.

 I don`t want those differences to exist. 

"I added two KPIs, while doing this reconciliation, their expressions are:"

 I would like to avoid set analysis calculations because the ammount of data is enormous and the will have performance issues 

Thank you,

Razvan

[razvan_brais]

@Vegar ,

 I`ve tried  your script.

 The problem is that on the HIST_DATE column it adds DATE`s that is shouldn`t be there.

 Example : if my current dates are : 1/1/2019 , 12/1/2019, 1/1/2020 , 12/1/2020 , after loading the script the hist_date values will be : 1/1/2019 , 2/1/2019, 12/1/2019 , 1/1/2020,2/1/2020, 12/1/2020, 1/1/2021 and that is not ok. 

 In hist_date I should have the initial values : 1/1/2019 , 12/1/2019, 1/1/2020 , 12/1/2020

Thank you,

Razvan

[ArnadoSandoval]

@razvan_brais 

Thanks for your reply, I will check the applications based on your reply, and I will not try SET analysis!

I will get back to you!

[HirisH_V7]

Yes your'e sample data doesn't have consecutive months which that code will work. Hope your'e getting what i m trying to say.

If you have consecutive months it will work.

[razvan_brais]

@HirisH_V7 ,

 I have tried with the sample script and changed the HIST_DATE from the sample file and it worked. 

 I will try with my actual script and come back with an update.

Thank you,

Razvan

[Vegar · Accepted solution]

In my script the values of January became the previous month value for february, thats why you get more dates thant what exists in your dec-jan sample data. In a larger sample the dates will inlign correcty to the following periods. 

It you find the data disk space to increase to much when addining the prev month transactions then you could always use join like this. You will also get rid of  the additonal HIST_DATE values if you choose to use LEFT join in your script.

Sample:
LOAD CLIENT_ID, 
     LOCATION_ID, 
     SEGMENT, 
     No_Month,
     HIST_DATE , 
     SALES as SALES
FROM
  SampleData.xlsx
  (ooxml, embedded labels, table is Sheet1)
;

LEFT JOIN (Sample)
LOAD CLIENT_ID, 
     LOCATION_ID, 
     SEGMENT, 
     num( month(addmonths(HIST_DATE, 1))) as Month_No,
     addmonths(HIST_DATE, 1) as HIST_DATE,  
     SALES as SALES_PREVIOUS
FROM
  SampleData.xlsx
  (ooxml, embedded labels, table is Sheet1)
;

[ArnadoSandoval]

Thanks @Vegar  I learned a lot by studying your code C

Thanks @razvan_brais  for asking this question, it is very enlightening. 

I will wait for Razvan_Brais to confirm the issue as resolved with Vegar code.

Regards,