topic Need help in correcting the formula in QlikView

Need help in correcting the formula

farheenayesha — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Need help in correcting the formula

Anil_Babu_Samineni — Thu, 03 Aug 2017 10:25:45 GMT

Try this?

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

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

Re: Need help in correcting the formula

farheenayesha — Thu, 03 Aug 2017 10:51:05 GMT

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.

Re: Need help in correcting the formula

Anil_Babu_Samineni — Thu, 03 Aug 2017 10:56:45 GMT

May be this for [Running Sales] ?

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

Re: Need help in correcting the formula

farheenayesha — Thu, 03 Aug 2017 11:03:06 GMT

Unfortunately getting the same incorrect result

Re: Need help in correcting the formula

Anil_Babu_Samineni — Thu, 03 Aug 2017 11:04:39 GMT

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

Re: Need help in correcting the formula

farheenayesha — Thu, 03 Aug 2017 11:13:44 GMT

Hi Ravi,

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

Re: Need help in correcting the formula

sunny_talwar — Thu, 03 Aug 2017 12:50:19 GMT

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

Re: Need help in correcting the formula

farheenayesha — Thu, 03 Aug 2017 13:40:53 GMT

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.

Re: Need help in correcting the formula

sunny_talwar — Thu, 03 Aug 2017 14:04:35 GMT

Check it out

Re: Need help in correcting the formula

sasiparupudi1 — Thu, 03 Aug 2017 17:18:49 GMT

Running Sales expression should be

=sum({<Month={'>=$(=min({1}Month))<=$(=max(Month))'}>}Sales)

Re: Need help in correcting the formula

farheenayesha — Mon, 07 Aug 2017 09:56:36 GMT

Hi Sunny,

Thank you so much for your help. You are super genius. I am not able to reply on your post hence replying from here. Thank you so much once again. I am not able to mark you answer as correct.

Thanks,

Ayeesha

Re: Need help in correcting the formula

farheenayesha — Mon, 07 Aug 2017 11:25:59 GMT

Thank you Sripad for your help. Your expression for running sales works perfect.