Help with calculate new goal(set analysis)

[#1]

Hi QV experts,

I need your help with the followingt requirements:

My requirements:

Show total number of accounts by month vs goals in combo chart. If certain month exceeds or doesn't meet the goal, re-calculate the goals for the rest of the year.

If Account in Jan = 300, which is greater than the goal(288), manager would like to see how we can re-calculate the goals for the rest of the year. We should divide (300-288)/11 and add this amount to the goals for Feb - Dec. After the recal Feb goal should be (150+12/11)

I'm having hard time showing the new goal line with set analysis.

Any help would be greatly appreciated!!

Data:

Date	COUNT	Goal
Jan	300	288
Feb	200	150
Mar	499	600
Apr	388	500
May	400	455

[#2]

To add to the previous post, I'd like to show the new goal as a static line which doesn't respond to any filters.

Thanks for the help!!

[sunny_talwar]

This only happens with the first time a goal is not meet or is this iterative for all months? Would you be able to provide the new goal numbers for all the months?

[#4]

Thank you Sunny -

This should happen for all of the months. New goals numbers should calculate based on the gaps. For example in March COUNT is 499 while goal is 600,  resulting in a gap of 201 which should spread across the rest of the months. Therefore the new goal for May should be (455+201).

Let me know if it makes sense!

Weiting

[sunny_talwar]

It does make sense, but this will take some time to work out an expression. I won't be able to spend that kind of time right now. So, if no body helps you by the evening, I will try to work on it

[#6]

Thank you Sunny!

I've been working on this chart for 2 days but couldn't figure out a viable solution. I thought set analysis should do the tricks but obviously something is missing...

Ideally I want to show this new goal as a reference line on the chart in parallel with the old goal. The two goals should NOT respond to any month/date filters.

I really appreciate your kind help!!

WY