Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Need a big help.
Assume, I got a data set as mentioned below,
My current Fiscal Year = 1/07/2018 to 30/06/2019
Product_ID | Transaction Date | Budgeted_Sale | Actual_Sales |
P_100 | 31-Jul-17 | 120 | 100 |
P_100 | 31-Aug-17 | 20 | 0 |
P_100 | 30-Sep-17 | 125 | 150 |
P_100 | 31-Oct-17 | 120 | 125 |
P_100 | 30-Nov-17 | 50 | 0 |
P_100 | 31-Dec-17 | 120 | 135 |
P_100 | 31-Jan-18 | 130 | 120 |
P_100 | 28-Feb-18 | 140 | 140 |
P_100 | 31-Mar-18 | 125 | 110 |
P_100 | 30-Apr-18 | 125 | 115 |
P_100 | 31-May-18 | 135 | 125 |
P_100 | 30-Jun-18 | 115 | 120 |
P_100 | 31-Jul-18 | 130 | 125 |
P_100 | 30-Aug-18 | 130 | |
P_100 | 30-Sep-18 | 130 | |
P_100 | 31-Oct-18 | 120 | |
P_100 | 30-Nov-18 | 120 | |
P_100 | 31-Dec-18 | 130 | |
P_100 | 31-Jan-19 | 125 | |
P_100 | 28-Feb-19 | 130 | |
P_100 | 31-Mar-19 | 125 | |
P_100 | 30-Apr-19 | 125 | |
P_100 | 31-May-19 | 130 | |
P_100 | 30-Jun-19 | 125 |
I want to present last three months Actual Sales and the variance (Actual Sales - Budget Sales) for the product "P_100". The output should looks like below,
Product_ID | Month July Actual Sales | Month July Variance | Month June Actual Sales | Month June Variance | Month May Actual Sales | Month May Variance |
P_100 | 125 | -5 | 120 | 5 | 125 | -10 |
I am also using a Master Calendar in my app.
The data for the above table will keep on adding at the end of each month and need to maintain the app without changing the parameter values of the expressions used in the app visualization window. Thus, basically I want to make the app more dynamic and less interaction with the app's script except for updating the source data with the upcoming month's Actual Sales data.
Note
I simply can't use the "Current Month" and "Last Month" flags according to my Master Calendar (which i have provided as a sample script below) simply because the Current Month value will set to Jun-19 and the Last Month value will set to May-19 cos I got budgeted sales values loaded for the entire current fiscal year.
Sample Master Calendar script
If(Month(TempDate) = Month($(vToday)), 1, 0) as IsCurrentMonth,
If(Month(AddMonths(TempDate,1)) = Month($(vToday)), 1, 0) as IsLastMonth,
I got a solution defining number of flags and accordingly calculating the values, but feels its not efficient.
Appreciate your valuable feedback.
Thank you.
Regards,
Andy
Hi,
Appreciate if someone could help me with respective to above discussion.
I was wondering whether I could use two Master Calendars to represents Budget Sales and the Actual Sales. If so, the data set has to be divided in to two with one date assigning to Actual Sales and the other for the Budget Sales.
My main intention is to use the IsCurrentMonth and the IsLastMonth flags to easily represent the respective last three months data.
Aprreciate your valuable feedback.
Thank you.
Regards,
Andy
Hi All,
I tried the below solution but ended up getting Looping issue.
Upon dividing the single data table in to two and used the below script to load the data in to qlik sense as below,
Actual_Data_Tbl:
LOAD
Product_ID,
"Transaction Date Actual",
Actual_Sales
FROM [lib://Andrew (centralwellingt_ckingham)/Data\Test\Sales_Data_Test.xlsx]
(ooxml, embedded labels, table is Sales_Actuals);
Budget_Data_Tbl:
LOAD
Product_ID,
"Transaction Date Budget",
Budgeted_Sale
FROM [lib://Andrew (centralwellingt_ckingham)/Data\Test\Sales_Data_Test.xlsx]
(ooxml, embedded labels, table is Sales_Budget);
In addition to above, I got two Master Calendars created to link Actual Sales and the Budget Sales.
MinMaxTemp:
LOAD
MIN([Transaction Date Actual]) AS MinDate,
MAX([Transaction Date Actual]) AS MaxDate
RESIDENT Actual_Data_Tbl;
LET vMinDate=NUM(PEEK('MinDate',0,'MinMaxTemp'));
LET vMaxDate=NUM(PEEK('MaxDate',0,'MinMaxTemp'));
LET vToday=$(vMaxDate);
CalTemp:
LOAD
DATE($(vMinDate)+ROWNO()-1) AS TempDate
AUTOGENERATE
$(vMaxDate)-$(vMinDate)+1;
DROP TABLE MinMaxTemp;
MasterCalendar:
LOAD
TempDate AS [Transaction Date Actual],
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month ,
.............................................................. (have not copied the entire thing since it takes more space)
Similarly I have created a separate master Calendar for the Budget sales as well.
Problem,
In addition to getting the looping issues, I am not getting the correct values for the expressions I am using.
Seek your valuable help.
Thanks.
Andy
Hi,
Finally was able to get the desired output by using set analysis and a single Master Calendar.
Thus, thought of just putting the simple expression in case if one want to do a similar work with actual and budget data to avoid the agony I went through.
Use of single Master Calendar according to the above scenario will spam from 1st of July 2017 to 30th of June 2019.
To calculate the last month actual sales,
=Sum(If(vFiscalMonthNumber=01 and FinYTDFlag=1 and FiscalMonthNum=$(vFiscalMonthNumber),Actual_Sales,
If(vFiscalMonthNumber=02 and FinYTDFlag=1 and FiscalMonthNum=$(vFiscalMonthNumber), Actual_Sales,
If(vFiscalMonthNumber>02 and FinYTDFlag=1 and FiscalMonthNum=$(vFiscalMonthNumber), Actual_Sales,0))))
Note
vFiscalMonthNumber is a variable I have defined in the load script and since i got Actual_Sales data up to month July, The vFiscalMonthNumber = 1 (Since my financial year spans from July to June).
Surely there can be a much improved way of accomplishing the desired outcome, If one come across a different method, appreciate if you could post it here on the discussion.
Thanks.
Regards,
Andy