topic Re: Adjust the sales objective values for future months in combo chart in App Development

Adjust the sales objective values for future months in combo chart

Haarika — Thu, 22 Jul 2021 18:43:21 GMT

Hi,

I need to show a comparion of Sales and sales objectives for the current year. I am using a combo chart for this purpose, in which Sales is a bar and sales objectives is a line. Point to note is that, sales objectives are set for the full year and sales are updated on a daily basis.

The critical requirement here is to calculate YTD difference of the actual sales and sales objectives for a given month and divide the difference with the number of future months remaining. Then add the final value obtained to the sales objectives of the future months.

Therefore, I am seeking help on how to frame the set analysis for the sales objectives to automatically get updated with each passing month.

I have attached an excel calculation for your reference.

@sunny_talwar , @Kushal_Chawda could you please provide your expert advice.

Many thanks in advance !

Haarika

Re: Adjust the sales objective values for future months in combo chart

Kushal_Chawda — Thu, 22 Jul 2021 11:40:03 GMT

@Haarika Sales and SO column in excel are expressions in your chart?

Re: Adjust the sales objective values for future months in combo chart

Haarika — Thu, 22 Jul 2021 12:37:07 GMT

@Kushal_Chawda thats correct. Sales - sum(sales) and S0 - sum(SO)

Re: Adjust the sales objective values for future months in combo chart

Haarika — Thu, 22 Jul 2021 13:18:30 GMT

Hi @Kushal_Chawda - I attached an updated excel for your reference. Hope this one explains the requirement better than the previous uploaded file. Please let me know if you have further questions. Thank you very much

Re: Adjust the sales objective values for future months in combo chart

Kushal_Chawda — Thu, 22 Jul 2021 14:34:19 GMT

@Haarika Do you want revised SO to be presented on graph?

Re: Adjust the sales objective values for future months in combo chart

Haarika — Thu, 22 Jul 2021 15:37:51 GMT

@Kushal_Chawda yes, that's correct.. the revised SO needs to be displayed as a trend line

Re: Adjust the sales objective values for future months in combo chart

Kushal_Chawda — Fri, 23 Jul 2021 12:08:39 GMT

@Haarika try below expression for SO

= if(RowNo(total)=1, sum(SO), if(RowNo(total)=2, rangesum(sum(SO), above(sum(SO)-sum(Sales))/11), if( RowNo(total)>=3, rangesum(above(Column(2)), (rangesum(above(Column(2),1, RowNo(total)))- rangesum( above(sum(Sales),1,RowNo(total))))/(12- RowNo(total)+1)))))

Note: Column(2) indicates that your SO expression is second expression in graph.

Re: Adjust the sales objective values for future months in combo chart

Haarika — Sat, 24 Jul 2021 15:21:50 GMT

@Kushal_Chawda, fabulous !!! amazing !! works as expected.... umpteen thanks to you 🙂

Re: Adjust the sales objective values for future months in combo chart

Haarika — Tue, 21 Sep 2021 01:42:48 GMT

Hi @Kushal_Chawda ,

I hope you are doing well !

There has been a slight modification in the business ask with regards to computing the Sales objective. I have attached an excel demonstrating the new methodology. Please refer to 'Product A' tab for sample data.

Key points to note:

a) for the completed months the Sales Objective should be made equal to the Sales (refer column B in excel)

b) the sum of revised SO for each month should always match the Sum of Original SO (refer column C in excel)

c) the adjusted SO trend line should display values calculated for the next month with no sales yet , i.e. as per the data provided SO values should be derived for Aug' 2021 (refer Column X in excel for calculations) and plotted as a trend and this should get updated when we receive sales for Aug'2021 and so on.

I find this requirement a bit tricky and hence need your assistance to solve it. Please let me know if you need further inputs

Re: Adjust the sales objective values for future months in combo chart

Kushal_Chawda — Tue, 21 Sep 2021 16:08:41 GMT

@Haarika Point a) says SO should be equal to Sales for completed months which is reflecting in Revised SO but point b) says revised so should equal to original SO, which is not the case.

Re: Adjust the sales objective values for future months in combo chart

Haarika — Tue, 21 Sep 2021 16:36:00 GMT

Hi @Kushal_Chawda , apologize for the confusion caused

point a) SO for the completed months should be equal to the corresponding month Sales

b) the sum of revised SO should always be equal to sum of the original SO (refer to row 14 in the excel)

hope this clarifies. thank you !

Re: Adjust the sales objective values for future months in combo chart

Haarika — Tue, 28 Sep 2021 00:24:13 GMT

Hello @Kushal_Chawda, any luck ? thank you !

Re: Adjust the sales objective values for future months in combo chart

Haarika — Wed, 06 Oct 2021 16:48:39 GMT

Hello @Kushal_Chawda, any luck with the solution?