Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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,
<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>
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;
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,
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
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;
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,
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:
I added some clients locally to test the logic moving into a new year; take a look at the attached Excel file.
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,
@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