Calculate sum of costs for variable number of months

[andymanu]

Hi,

I got a data set and I am calculating the Variance of Actual vs Budgeted Sales.

Assume the financial year is from July 1st to June 30th of the following year. Please refer the below sample data set for product A's sales details. (For simplicity, I am recording the sales values at the end of each month).

Product A Sales
Product_ID	Sales Month	Actual Sales	Budgeted Sale
P_100	31/July/2017	100	120
P_100	31/August/2017	115	125
P_100	30/September/2017	150	125
P_100	31/October/2017	125	120
P_100	30/November/2017	120	115
P_100	31/December/2017	135	120
P_100	31/January/2018	120	125
P_100	28/February/2018	140	125
P_100	31/March/2018	110	125
P_100	30/April/2018	105	125
P_100	31/May/2018	125	130
P_100	30/June/2018	120	120
P_100	31/July/2018	125	125
P_100	30/August/2018		130
P_100	30/September/2018		130
P_100	31/October/2018		120
P_100	30/November/2018		120
P_100	31/December/2018		130
P_100	31/January/2019		125
P_100	28/February/2019		130
P_100	31/March/2019		125
P_100	30/April/2019		125
P_100	31/May/2019		130
P_100	30/June/2019		125

The above data set got two years of data and my requirements are;

1. When I open the app, I want to see the below table information about the sales for Product A.

Calculate the sum of actual Sales, sum of budgeted sales and variance  as shown below from 31/July/2017 to 31/July/2018.

Product_ID	Actual Sales	Budgeted Sales	Variance
P_100	1465	1475	10

Note

When adding the budgeted sales values, I want to display the sum of budgeted sales from 31/July/2017 to 31/July/2018 and the variance also should be from 31/July/2017 to 31/July/2018 (last month).

I am using a master calendar in my app.

2. Assume I got Year and the respective month on the app visualization window using a filter pane so that a user can select a fiscal year and a month to visualize the respective Year's and Month's data. Suppose a user selects year as 2018 and the month as January, the above table should depict the sales information for the selected criteria as shown below,

Product_ID	Actual Sales	Budgeted Sales	Variance
P_100	120	125	5

Could anyone help me with the above which I wanted to achieve since some time.

Appreciate, even someone could help me to accomplish at least part of the tasks.

Thanks in advance and it would be a great help for me.

Kind regards,

Andy

[niclaz79]

Hi,

1. The easiest way to deal with a fragmented calendar year is to add an additional date field that indicates the fiscal year. In your master Calendar, you can add a field called FiscalYear and then for all dates 31 July 2017 - 31 July 2018 you set FiscalYear to 2018. This way you can now select the entire scope of dates just referencing the FiscalYear you want to see (Sum({<[Sales Month] =, FiscalYear = {$(=Max(FiscalYear))}>}[Actual Sales]) )

For your number 2, this should already work considering you have a master calendar connected to the date. If it doesn't, the calendar is not correctly connected.

[paola_valenti]

1st question:

After having load your data, in a table have:

DIMENSION: Product_ID

EXPRESSIONS:

1) =sum({<SalesMonth={">=$(=AddMonths(max({<ActualSales={">0"}>}SalesMonth),-12)) <$(=max({<ActualSales={">0"}>}SalesMonth))"}>}ActualSales)

-> Actual Sales

2) =sum({<SalesMonth={">=$(=AddMonths(max({<ActualSales={">0"}>}SalesMonth),-12)) <$(=max({<ActualSales={">0"}>}SalesMonth))"}>}BudgetedSale)

-> Budgeted Sale

3) =sum({<SalesMonth={">=$(=AddMonths(max({<ActualSales={">0"}>}SalesMonth),-12)) <$(=max({<ActualSales={">0"}>}SalesMonth))"}>}BudgetedSale-ActualSales)

-> Variance

Set Analysis defines 12 months interval

FROM (>=) is AddMonths(max({<ActualSales={">0"}>}SalesMonth),-12)

TO (<) is max({<ActualSales={">0"}>}SalesMonth)

When you add a value in ActualSales, Month Interval will switch

[paola_valenti]

2nd question:

if you have master calendar with Month and Year and user filter a month and a year then a simple table, without set analysis will answer your question:

DIMENSION: Product_ID

EXPRESSIONS:

1) =sum(ActualSales)

2) =sum(BudgetedSale)

3) =sum(BudgetedSale-ActualSales)

If the problem is that filter will affect set analysis in 1st question, then you can add {1} in expression in 1st table

[paola_valenti]

Here a app with your tables:

[andymanu]

Thank you so much for the effort.

Unfortunately, I am unable to open the .qvf file.

I'll try a different option to get it open.

If you got an alternative, it would be great at this stage.

Thank you.

Regards,

Andy

[paola_valenti]

Aren't you using Qlik Sense Desktop?

Please copy .qvf file in Your PC in Documents/Qlik/Sense/Apps

Then you can open it in Qlik Sense Desktop.

Here a print of my app, I don't know if is useful

[andymanu]

Thanks Paola,

I am at my work place and does not have access to the qlik sense server.

I'll try the code on my laptop at home.

Regards,

Andy

[andymanu]

Hi Paola,

I tried it on my personal computer.

Yes it's working, However, the table 2 budget figures are not correct despite it shows the correct variance based on the user selected month.

I think that is because it's simply calculating the total of budget cost.

Also, if you select a month only without selecting a year, it will calculate the total of the budget cost for the month across all the available years (Oneway it is difficult to avoid too).

However, thanks for your valuable guidance.

Please also could you please clarify bit further about using a single table if possible to demonstrate both of my queries.

Regards,

Andy

[paola_valenti]

I'm not sure I understand what your goal is.

I modified my app this way to filter month and year in a single choice.