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 sum of costs for variable number of months

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_IDSales MonthActual SalesBudgeted Sale
P_10031/July/2017100120
P_10031/August/2017115125
P_10030/September/2017150125
P_10031/October/2017125120
P_10030/November/2017120115
P_10031/December/2017135120
P_10031/January/2018120125
P_10028/February/2018140125
P_10031/March/2018110125
P_10030/April/2018105125
P_10031/May/2018125130
P_10030/June/2018120120
P_10031/July/2018125125
P_10030/August/2018
130
P_10030/September/2018130
P_10031/October/2018120
P_10030/November/2018120
P_10031/December/2018130
P_10031/January/2019125
P_10028/February/2019130
P_10031/March/2019125
P_10030/April/2019125
P_10031/May/2019130
P_10030/June/2019125

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_IDActual SalesBudgeted SalesVariance
P_1001465147510

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_IDActual SalesBudgeted SalesVariance
P_1001201255

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

12 Replies
niclaz79
Partner - Creator III
Partner - Creator III

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
Creator
Creator

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
Creator
Creator

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
Creator
Creator

Here a app with your tables:

andymanu
Creator II
Creator II
Author

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
Creator
Creator

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

Print.PNG

andymanu
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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
Creator
Creator

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.

Print2.PNG