Combo Chart - commulative field as line

Q&A
, , ,
1

Hello!

I have a data where there is a budget set for each product (yearly budget), and also the costs of the product per month. For the first month, the budget is yearly_budget/12, for the next month it’s yearly_budget-costs_1st_month/11, for the next one it’s yearly_budget-(costs_1st_month+costs_2nd_month)/10 and etc, i.e. each month budget is recalculated.

I’ve created a caulculated filed for an adjusted budget and if we look into the table, the data is correct.

image
image1046×346 44.4 KB

What I want to achive is to have a combo chart where bars are product costs, and line is the adjusted budget. When I have the following setup it’s saying that calculation reference is missing in the fields

image
image1598×728 55.1 KB

But when I swap costs and adjusted budget, it then shows it properly

image
image1591×665 55.6 KB

Is it possible to achive the first case, where costs are bars and adjusted budget is a line?

Formula for adjusted budget
ifelse(
isNull({running_costs}),
avg({yearly_budget})/12,
(avg({yearly_budget})-{running_costs})/(avg(13-{pt_month}))
)
Formula for running_costs
lag(
runningSum(sum(costs), [{pt_month} ASC], [{md_product_name}]),
[{pt_month} ASC],
1,
[{md_product_name}]
)

Best regards,
Kseniya

2

Hello @ksenstad, I am wondering if we could maybe move the calculations to the dataset if that would resolve the issue. I tried to create an alternate version of the adjusted budget field to make it into a LAC-W aggregation. That would help resolve the display issues, but I couldn’t get a version of it that wouldn’t also include the runningSum function which is likely causing some of the problems.

I am curious if the visual will display the way you are wanting though if the calculated fields are added at the dataset level. If not, we may need to find an alternate way to return the values you are expecting.

3

Hello @ksenstad, I wanted to reach out since we have not heard back from you on this request. In order for me to help guide you further, could you please follow-up on my previous response and let me know if that helps to resolve the issue you are facing. If we do not hear back from you in 3 days, I will close out this topic. Thank you!

4

Hi @ksenstad,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.

Thank you