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
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,
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.
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
@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
@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
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!
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.
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
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)
;
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,