topic Re: Previous month sales in loading script in App Development

Previous month sales in loading script

razvan_brais — Tue, 28 Apr 2020 07:56:41 GMT

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

Re: Previous month sales in loading script

ArnadoSandoval — Tue, 28 Apr 2020 13:28:35 GMT

Hi @razvan_brais

This script is close to give you the solution, it is not bringing the previous month sales for any month of January, I did not include that bit of code for you to complete it.

// // Load your data to a Temp_Data table // Temp_Data_1: 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; // Order your data by Location_ID, Client_Id, Hist_Date, No_Month Temp_Data_2: LOAD RowNo() As Record_No, CLIENT_ID, CLIENT_NAME, LOCATION_ID, LOCATION_NAME, SEGMENT, BUSSINES_FLG, NPL, RATING, HIST_DATE, No_Month, MonthMonth, SALES, // Previous(SALES) as PREV_SALES Resident Temp_Data_1 ORDER BY LOCATION_ID, CLIENT_ID, HIST_DATE, NO_MONTH; // we do not need Temp_Data_1 Drop Table Temp_Data_1; // Bring previous months sales DATA: LOAD RowNo() CLIENT_ID, CLIENT_NAME, LOCATION_ID, LOCATION_NAME, SEGMENT, BUSSINES_FLG, NPL, RATING, HIST_DATE, No_Month, MonthMonth, SALES, If( Record_No = 1, 0, if(PREV_CLIENT_ID = CLIENT_ID and PREV_LOCATION_ID = LOCATION_ID and PREV_HIST_DATE = HIST_DATE, Peek('SALES', Num#(Record_No - 1), SALES), 0) As PREV_SALES; LOAD *, If( Record_No = 1, 0, Peek('CLIENT_ID', Num#(Record_No - 1), SALES)) As PREV_CLIENT_ID, If( Record_No = 1, 0, Peek('LOCATION_ID', Num#(Record_No - 1), SALES)) As PREV_LOCATION_ID, If( Record_No = 1, 0, Peek('HIST_DATE', Num#(Record_No - 1), SALES)) As PREV_HIST_DATE, If( Record_No = 1, 0, Peek('NO_MONTH', Num#(Record_No - 1), SALES)) As PREV_NO_MONTH' Resident Temp_Data_2; // We do not need Temp_Data_2 Drop Table Temp_Data_2;

Hope this helps,

Re: Previous month sales in loading script

razvan_brais — Wed, 29 Apr 2020 10:44:08 GMT

Hy , @ArnadoSandoval ,

Thank you for your answer . This helped a lot. I just made some adjustments to the script and it`s working fine.

The only problem appears when I`m trying to compare January-2020 with December-2019. My guess is because there might be some differences between CLIENTS ( I might have less clients in JANUARY than in December) , and the script does the calculations only for those that are in JANUARY.

Or there is some other things?

Thank you again,

Razvan

Re: Previous month sales in loading script

ArnadoSandoval — Wed, 29 Apr 2020 12:00:23 GMT

Hi @razvan_brais

You need to improve the condition, on Januaries the current year and previous year are different, you need to introduce this logic into the condition already there.

Hope the hint helps,

Re: Previous month sales in loading script

razvan_brais — Wed, 29 Apr 2020 13:43:44 GMT

Thank you @ArnadoSandoval for your answer, but unfortunately I can`t find any solution 😞

Re: Previous month sales in loading script

ArnadoSandoval — Wed, 29 Apr 2020 13:56:28 GMT

@razvan_brais

Did you try to improve LOAD statement's If condition? if you did, would you share the code.

Can you post some sample data? or share small Excel file sample.

Regards,

Re: Previous month sales in loading script

razvan_brais — Wed, 29 Apr 2020 14:55:24 GMT

This is the code I wrote.

I followed your sample code and modified it a bit.

I can`t attach any sample file because I`m not allowed.

Temp_Data_1:
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
FROM TABLE;

Temp_Data_2:
LOAD
RowNo() As Record_No,
CLIENT_ID,
CLIENT_NAME,
LOCATION_ID,
LOCATION_NAME,
SEGMENT,
BUSSINES_FLG,
NPL,
RATING,
HIST_DATE,
No_Month,
MonthMonth,
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,
CLIENT_NAME,
LOCATION_ID,
LOCATION_NAME,
SEGMENT,
BUSSINES_FLG,
NPL,
RATING,
HIST_DATE,
No_Month,
MonthMonth,
SALES,
If( Record_No = 1, 0, if(PREV_CLIENT_ID = CLIENT_ID and
PREV_LOCATION_ID = LOCATION_ID and
//changed WITH PREV_SEGMENT
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('NO_MONTH')) As PREV_NO_MONTH'
Resident Temp_Data_2;

Drop Table Temp_Data_2;

Re: Previous month sales in loading script

razvan_brais — Wed, 29 Apr 2020 15:23:14 GMT

I have made a count over CLIENT_ID and I observed that in 2020 i have a number of , lets say 100 000 of clients , and in 2019 I have a lot more clients , lets say 150 000 clients.

When I`m selecting January 2020 it olny shows values for 100 000 clients , and PREVIOUS_SALES is calculated only for those clients.

Re: Previous month sales in loading script

razvan_brais — Thu, 30 Apr 2020 10:53:48 GMT

@ArnadoSandoval , i could modify the if statement something like this :

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 don`t know how can i refer to December values') )) as PREV_SALES,

Can you please help me?

Thank you

Re: Previous month sales in loading script

ArnadoSandoval — Thu, 30 Apr 2020 13:38:31 GMT

@razvan_brais

I did not notice the introduction of the SEGMENT column as part of the logic, I did not include it in my previous script; that could be part of the problem; Would you please share with me your current script? (I will add a picture illustrating the steps) also, Would you please show me your data, either attach an Excel file with small sample, or take an screenshot.

I think the issue you are having is with the introduction of the SEGMENT column.

This is how to include the script in your reply ...

Click on the ... (3 dots)
Click on the </> you will get a pop-up windows/form, paste your script in there and click OK.

Thanks,

Re: Previous month sales in loading script

razvan_brais — Thu, 30 Apr 2020 14:20:43 GMT

@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;

Re: Previous month sales in loading script

ArnadoSandoval — Fri, 01 May 2020 01:10:16 GMT

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,

Re: Previous month sales in loading script

aaditya0803 — Fri, 01 May 2020 09:57:44 GMT

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

Re: Previous month sales in loading script

razvan_brais — Sat, 02 May 2020 05:40:14 GMT

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;

Re: Previous month sales in loading script

ArnadoSandoval — Sat, 02 May 2020 07:37:47 GMT

@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,

Re: Previous month sales in loading script

razvan_brais — Sat, 02 May 2020 07:52:44 GMT

@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

Re: Previous month sales in loading script

Vegar — Sat, 02 May 2020 11:27:07 GMT

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;

Re: Previous month sales in loading script

ArnadoSandoval — Sat, 02 May 2020 11:38:46 GMT

@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,

Re: Previous month sales in loading script

ArnadoSandoval — Sat, 02 May 2020 14:34:08 GMT

@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,

Re: Previous month sales in loading script

razvan_brais — Sat, 02 May 2020 16:02:34 GMT

@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