Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andymanu
Creator II
Creator II

Calculate last three months sales

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_IDTransaction DateBudgeted_SaleActual_Sales
P_10031-Jul-17120100
P_10031-Aug-17200
P_10030-Sep-17125150
P_10031-Oct-17120125
P_10030-Nov-17500
P_10031-Dec-17120135
P_10031-Jan-18130120
P_10028-Feb-18140140
P_10031-Mar-18125110
P_10030-Apr-18125115
P_10031-May-18135125
P_10030-Jun-18115120
P_10031-Jul-18130125
P_10030-Aug-18130
P_10030-Sep-18130
P_10031-Oct-18120
P_10030-Nov-18120
P_10031-Dec-18130
P_10031-Jan-19125
P_10028-Feb-19130
P_10031-Mar-19125
P_10030-Apr-19125
P_10031-May-19130
P_10030-Jun-19125

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_IDMonth July Actual SalesMonth July VarianceMonth June Actual SalesMonth June VarianceMonth May Actual SalesMonth May Variance
P_100125-51205125-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

3 Replies
andymanu
Creator II
Creator II
Author

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

andymanu
Creator II
Creator II
Author

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

andymanu
Creator II
Creator II
Author

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