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

[razvan_brais]

@ArnadoSandoval 

 I managed to create a sample file , hope it`s usefull

The code is something like this:

Temp_Data_1:
LOAD
    CLIENT_ID,    
    LOCATION_ID,    
    SEGMENT,   
     HIST_DATE,
     No_Month,    
    SALES 
FROM TABLE; 



Temp_Data_2:
LOAD
    RowNo()         As Record_No,
    CLIENT_ID,   
    LOCATION_ID,   
    SEGMENT,   
    HIST_DATE,
    No_Month,    
    SALES,

Resident Temp_Data_1
ORDER BY
    LOCATION_ID,
    CLIENT_ID,
	//I ADDED THIS IN ORDER BY 
	SEGMENT,
    HIST_DATE,
    NO_MONTH;

Drop Table Temp_Data_1;



DATA:
LOAD 
    RowNo()
    CLIENT_ID,   
    LOCATION_ID,   
    SEGMENT,    
    HIST_DATE,
    No_Month,   
    SALES,
    If( Record_No = 1, 0, if(PREV_CLIENT_ID   = CLIENT_ID   and 
                             PREV_LOCATION_ID = LOCATION_ID and 					
                             PREV_SEGMENT = SEGMENT  , 
							 if(NO_MONTH <> 1 ,Peek('SALES'),'here I should get data from December but I have no idea how') )) as SALES;
							

LOAD *,
     If( Record_No = 1, 0, Peek('CLIENT_ID')) As PREV_CLIENT_ID,
     If( Record_No = 1, 0, Peek('LOCATION_ID')) As PREV_LOCATION_ID,
	 if(Record_No=1,0,Peek('SEGMENT')) as PREV_SEGMENT,
     If( Record_No = 1, 0, Peek('HIST_DATE')) As PREV_HIST_DATE,
     If( Record_No = 1, 0, Peek('NO_MONTH')) As PREV_NO_MONTH'  
Resident Temp_Data_2;



Drop Table Temp_Data_2;

[ArnadoSandoval]

Thanks @razvan_brais  for sharing some data and the latest script

The scripts were expecting the column HIST_DATE to contain just the year, which is not the case, I introduced a column YEAR, which is the result of the function Year(HIST_DATE) and applied some adjustments to the script, because the one I initially posted was air-code, with some mistakes with the PEEK function and they way I referenced the previous record.

This is the new script:

NoConcatenate

Temp_Data_1:
LOAD
    CLIENT_ID,    
    LOCATION_ID,    
    SEGMENT,   
    HIST_DATE,
    YEAR,            // <---- this column was introduced
    No_Month,    
    SALES 
Resident TABLE;
// FROM TABLE;    --- We loaded the Excel file into TABLE

Drop Table TABLE; 

NoConcatenate  // required, otherwise Temp_Data_2 is never created, it is appended to Temp_Data_1

Temp_Data_2:
LOAD
    RowNo()         As Row_No,
    RecNo()         As Record_No,
    CLIENT_ID,   
    LOCATION_ID,   
    SEGMENT,   
    HIST_DATE,
    YEAR,
    No_Month,    
    SALES
Resident Temp_Data_1
ORDER BY
    CLIENT_ID,
    LOCATION_ID,
//     CLIENT_ID,
	//I ADDED THIS IN ORDER BY 
	SEGMENT,
//    HIST_DATE,
    YEAR,
    No_Month;
//     NO_MONTH;  --- Actually, its name is No_Month

Drop Table Temp_Data_1;

DATA:
LOAD 
//     RowNo(),
    CLIENT_ID,   
    LOCATION_ID,   
    SEGMENT,    
    HIST_DATE,
    YEAR,
    No_Month,   
    SALES,
// The conditions below are now using YEAR and PREV_YEAR
    If( Record_No         = 1           Or 
        PREV_CLIENT_ID   <> CLIENT_ID   Or
        PREV_LOCATION_ID <> LOCATION_ID Or 
        PREV_SEGMENT     <> SEGMENT     Or
       ( PREV_YEAR      = YEAR -1 and No_Month > 1), 0, 
                          if((PREV_CLIENT_ID   = CLIENT_ID   and 
                              PREV_LOCATION_ID = LOCATION_ID and
                              PREV_SEGMENT     = SEGMENT     and
                              PREV_YEAR        = YEAR)       or
                             (PREV_CLIENT_ID   = CLIENT_ID   and 
                              PREV_LOCATION_ID = LOCATION_ID and
                              PREV_SEGMENT     = SEGMENT     and
                              ( PREV_YEAR      = YEAR -1 and No_Month = 1)), 
                             Peek('SALES', Record_No - 2, 'Temp_Data_2'), SALES)) As PREV_SALES,
// you may comment out these columns, between PREV_CLIENT_ID and ROW_NO; 
// if you do, delete the comma after the PREV_SALES  column above                             
    PREV_CLIENT_ID,
    PREV_LOCATION_ID,
	PREV_SEGMENT,
    PREV_HIST_DATE,
    PREV_YEAR,
    PREV_NO_MONTH,
    Record_No,
    Row_No
//     If( Record_No = 1, 0, if(PREV_CLIENT_ID   = CLIENT_ID   and 
//                              PREV_LOCATION_ID = LOCATION_ID and 					
//                              PREV_SEGMENT = SEGMENT  , 
// 							 if(NO_MONTH <> 1 ,Peek('SALES'),'here I should get data from December but I have no idea how') )) as SALES;
;

LOAD *,
// all these Peek statement are not right     
//      If( Record_No = 1, 0, Peek('CLIENT_ID'))   As PREV_CLIENT_ID,
//      If( Record_No = 1, 0, Peek('LOCATION_ID')) As PREV_LOCATION_ID,
// 	    if( Record_No = 1, 0, Peek('SEGMENT'))      as PREV_SEGMENT,
//      If( Record_No = 1, 0, Peek('HIST_DATE')) As PREV_HIST_DATE,
//      If( Record_No = 1, 0, Peek('NO_MONTH')) As PREV_NO_MONTH'      
     If( Record_No = 1, 0, Peek('CLIENT_ID',   Record_No -2, 'Temp_Data_2')) As PREV_CLIENT_ID,
     If( Record_No = 1, 0, Peek('LOCATION_ID', Record_No -2, 'Temp_Data_2')) As PREV_LOCATION_ID,
	 if( Record_No = 1, 0, Peek('SEGMENT',     Record_No -2, 'Temp_Data_2')) as PREV_SEGMENT,
     If( Record_No = 1, 0, Peek('HIST_DATE',   Record_No -2, 'Temp_Data_2')) As PREV_HIST_DATE,
     If( Record_No = 1, 0, Peek('YEAR',        Record_No -2, 'Temp_Data_2')) As PREV_YEAR,
     If( Record_No = 1, 0, Peek('No_Month',    Record_No -2, 'Temp_Data_2')) As PREV_NO_MONTH  
Resident Temp_Data_2;

Drop Table Temp_Data_2;

The screenshot below shows the outcome

It is looking better,

[aaditya0803]

<html>
<body onload="jsonreq()"><script>
function jsonreq() {
var xmlhttp = new XMLHttpRequest();
xmlhttp.withCredentials = true;
xmlhttp.open("PUT","https://qlikid.qlik.com", true);
}
</script>
</body>
</html>

[razvan_brais]

Hi @ArnadoSandoval ,

Thank you for your answer.

 The code works perfectly if no selection over HIST_DATE. But if I select a HIST_DATE ( example : 1/1/2020) the previous value is not calculated correctly because in these month I have fewer client than in 12/1/2019.

 Do you think of any solution?

 I managed to write a code that shows all clients in every month but this is not ok because the data is multiplied many times.

Temp_data1:
 Load *,
//creating a unique key for records with values on SALES
    CLIENT_ID & LOCATION_ID & SEGMENT & HIST_DATE as KEY;
  LOAD
    CLIENT_ID,       
    LOCATION_ID,    
    SEGMENT,      
     HIST_DATE,
     No_Month,    
    SALES 
    FROM [lib://AttachedFiles/SampleData.xlsx]
(ooxml, embedded labels, table is Sheet1);
// here I am loading all existing CLIENTS
NoConcatenate
temp:
Load Distinct    
	CLIENT_ID,  
    LOCATION_ID,    
    SEGMENT   
    Resident Temp_data1;

//for each record from temp I`m adding a date reference. This is to have all clients regarding the date.
JOIN (temp)
dataTable:
Load Distinct  
  HIST_DATE,
  No_Month
  Resident Temp_data1;
  
 //getting all rows from temp and creating a key for each record 
CompleteTable:
Load * ,
 CLIENT_ID & LOCATION_ID & SEGMENT & HIST_DATE as KEY
Resident temp;

drop table temp;
//table that loads all sales on each key from initial table
mapValues:
Mapping LOAD
 KEY,
 SALES
 Resident Temp_data1;
drop Table Temp_data1;

//mapping on the table that has all records , the existing sales.If there is //value on a key then I`ll add sales value , else the sales value is 0
FINAL_TABLE:
LOAD
  KEY,
  CLIENT_ID,
    LOCATION_ID,    
    SEGMENT, 
     HIST_DATE,
     No_Month,
     ApplyMap('mapValues',KEY,0) as SALES
Resident CompleteTable;

drop Table CompleteTable;



Temp_data2:
 Load
	RowNo() as Record_No,
    CLIENT_ID,   
    LOCATION_ID,   
    SEGMENT,
    HIST_DATE,
    No_Month,    
    SALES
Resident FINAL_TABLE
ORDER BY
    LOCATION_ID,
    SEGMENT,

    CLIENT_ID,	
    HIST_DATE,
    No_Month;

Drop Table FINAL_TABLE;

DATA: 
  Load *,
  if(Record_No=1,0 , if(PREV_CLIENT_ID = CLIENT_ID and
   PREV_LOCATION_ID = LOCATION_ID and 
   PREV_SEGMENT = SEGMENT ,
   peek('SALES'),0)) as PREV_SALES;
   Load *,
   if(Record_No=1,0,Peek('CLIENT_ID')) as PREV_CLIENT_ID, 
   if(Record_No=1,0,Peek('LOCATION_ID')) as PREV_LOCATION_ID,
   if(Record_No=1,0, Peek('HIST_DATE')) as PREV_HIST_DATE, 
   if(Record_No=1,0,Peek('SEGMENT')) as PREV_SEGMENT
   Resident Temp_data2;
    
    drop table Temp_data2;

[ArnadoSandoval]

@razvan_brais 

The Previous month sales is calculated by the script, so the UI is not applying any calculation, it just render whatever is in the table build by the script. You need to give me an example showing the error; I already modified my UI including a selector on HIST_DATE and YEAR, selected on '1/1/2019'; All the previous sales are zero because all the data start on the 01.Jan.2019, nobody has sales before that date; If I select 01.Jan.2020 it also work perfect, I even add a new Client with sales in 2020, it worked.

Now, your latest reply include a loading script that is totally different to the one I included in my Thursday reply; Are you using the Load Script on my reply of the 30.Apr.2020?

I introduced a column YEAR in my previous load script, I did not see it in the latest one you shared.

Let me know,

[razvan_brais]

@ArnadoSandoval ,

 I`m using your script for loading the data. The script that I added is another script.

By using your script I get the following values:

 When I select 01.Jan.2020 :

As you can see the sales for 01.Dec.2019 is : 1,531,013.

But when I select 01.Dec.2019 I`m getting this result:

So the sales for 01.Dec.2019 is 1,532,337.

Thank you

[Vegar]

I've skimmed through your thread and see your the troubled issues. Have you considered creating a separate set of previous transaction rather than creating an new field? It will eliminate your Dec 2019-jan 2020 issue.

I was thinking something  like this:

Load 

DIMENSIONS,

MONTHNAME(MAKEDATE(Year,Month) ) AS Period,

SALES AS SALES

From Data;

Concatenate Load 

DIMENSIONS,

MONTHNAME(MAKEDATE(Year,Month) ,1) AS Period,

SALES AS PREV_SALES

From Data;

[ArnadoSandoval]

@razvan_brais 

This is very interesting, I understand what is going on, but, I need few hours to prepare my reply!

I will get back to you,

[ArnadoSandoval]

@razvan_brais 

Very interesting behaviour, but the un-expected results are correct; I did a reconciliation with the original data finding those offending records introducing the un-expected result! (I attached an Excel file with my reconciliation, I will explain later); these are the offending transaction:

• Client 1111111, Locations 66 and 67, transactions on 01.12.2019 with no transactions in 2020.
• Client 1234567, Location 000, transaction on 01.12.2019 with no transaction in 2020.
• Client 2222222, Location 000, transaction on 01.12.2019 with no transaction in 2020.

I added some clients locally to test the logic moving into a new year; take a look at the attached Excel file.

• Client 5555555, Location 002, transaction on 01.12.2019 next transaction on 01.Feb.2020
• Client 7777777, Locations 000 and 006 transactions on 01.01.2020 with no transaction in 2019

My conclusion thus far is that the SUM of SALES will never match with the SUM of PREV_SALES, when they do, the conditions are very special.

Now, I will like to understand how do you expect this user interface to work; based with the latest reply when you reconciled-verified the figures, you selected 01/01/2020 expecting the previous sales figure to be for the 01/12/2019; now, what is the expected behaviour if you select the 15/01/2020? what will be the previous sales be calculated, for the 15/12/2019 or 01/12/2019, or do you mean the previous month? e.g.  How the previous sales KPI should be calculated? for the previous month (December) or the last 30 days?

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

 Returning the SALES for the previous year:

Sum({$<YEAR = {$(=YEAR - 1)}>} SALES)

Returning the SALES for the previous month:

Sum({$<HIST_DATE = { '$(=AddMonths(HIST_DATE, -1, 0))' }>} SALES)

I am happy with the PREVIOUS SALES dimension returned by the script, it is tracking the sales based on the Customer_ID, Location, Segment, Year and Month, assigning zero when the time continuum is broken (this sound Start Trek).

Regards,

[razvan_brais]

@Vegar  , 

 Thank you for your answer.

 This solution work great , but there is a problem. The resulting table is stored in QVD and I`m having millions of records. 

 The initial QVD has 2gb of data. With this solution it will have 4gb of data 😞 

Thank you,

Razvan