Need help in correcting the formula

[farheenayesha]

Hi,

I need help in calculating an expression called 'Needed AWS to hit the Goal'. Please find the formaula below

[Needed AWS to hit the Goal] = ([Total Goal] - [Running sum of Sales])/[Remaining Weeks]

Total Goal = Sum(Goal)

Running sum of Sales = sum(Sales)      //Suppose if I select Apr in month filter then the sum(sales) should take values from Jan till Apr. I am not able to achieve this. Please help me to achieve this as well. Similarly if i select Mar it should take values from Jan to Mar.

Remaining Weeks = avg([No . of Weeks])  //  Ex. if i select Apr the remaining weeks would be avg of [No . of Weeks] for May.

The graph should show up values as below image.( the below image is from tableau)

I want to display AWS staring from Jan to month selected. Ex. If I select Apr i want to show AWS from Jan to APR and if i select Mar then AWS should show from Jan to MAR only. All other values should be blank as shown in the above picture.

similarly, I want to display [Needed AWS to hit the Goal] as below.

Ex. If I select Apr i want to show [Needed AWS to hit the Goal] from May to Jun.(Forecasted values)   // Note : data is only till June

I already have calculations and graph in place. However i just need your help to correct the formula to get the correct values as below

[Anil_Babu_Samineni]

Try this?

(Sum(Sales) - Sum({<Month = {">=$(=Min(Month)) <=$(=Max(Month))"}>}Sales)) /

Avg({<Month = {">=$(=Max(Month))"}>}[No. of Weeks])

[farheenayesha]

Hi Anil,

     Thanks for your Quick Response.

Using your expression i am not getting running sales and remaining values correctly . Please find the snap shot below.

I want to get running sales as 84,967,076 and remaining weeks as 9. Please find second excel sheet attached  for the reference in the main post. 

Please help me to get the values.

[Anil_Babu_Samineni]

May be this for [Running Sales] ?

Sum({<Month = {">=$(=Min(Month)) <=$(=Max(Month))"}, Year = {'$(=Max(Year))'}>}Sales)

[farheenayesha]

Unfortunately getting the same incorrect result

[Anil_Babu_Samineni]

Yes, I know that. Please provide application if possible as short (300 KB - Max)

[farheenayesha]

Hi Ravi,

     Please find the reduced qvw file attached in the main post.

[sunny_talwar]

Why do you need 84,967,076? That number will be Accumulation of Accumulation? I just don't see a point of doing this?

[farheenayesha]

Hi Sunnny,

     I may be wrong as well. I am just trying to achieve 'Needed AWS to hit the goal' as 5173433 for May, when APR is selected. Please refer the runrate excel file and the above image in main post.

[sunny_talwar · Accepted solution]

Check it out