cancel
Showing results for 
Search instead for 
Did you mean: 

Creation of an weighted average - SAC

Deb_
Participant
0 Kudos
536

Hey,

I try to calculate an average annual price (in my case - 2021) that is, sales divided by the quantity and put the result in the first month (in my case - January 2021)

In practice what I get is an average price calculation for each month separately, and in January the sum of all the results:

see below the code (data action) and result:

BY the way - if I do the same for the the counter only or for the nominator only - it works perfectly - the issue begin when I divide them

how can I do it (dividing by 12 gives of course a close but wrong result)

Thanks

Deb

MEMBERSET [d/Date]= %FROM_DATE% TO %TO_DATE%

VARIABLEMEMBER #TOTAL_INCOME_NET OF [d/ACCOUNT]
VARIABLEMEMBER #TOTAL_QTY_BTL OF [d/ACCOUNT]

DATA([d/ACCOUNT]=#TOTAL_INCOME_NET)=
RESULTLOOKUP([d/ACCOUNT]="ACT_INCOME_NET",[d/Version]="public.Actual")

DATA([d/ACCOUNT]=#TOTAL_QTY_BTL)=
RESULTLOOKUP([d/ACCOUNT]="SALES_QTY_BTL_ACT",[d/Version]="public.Actual")

DATA([d/ACCOUNT]="AVRG_YEARLY_PRICE",[d/Date]=FIRST())=
RESULTLOOKUP([d/ACCOUNT]=#TOTAL_INCOME_NET)/RESULTLOOKUP([d/ACCOUNT]=#TOTAL_QTY_BTL)


N1kh1l
Active Contributor
0 Kudos
dd5

hey,

What Avg price you expect in January 193300/14723 = 13.1291?

Regards

Nikhil

Deb_
Participant
0 Kudos

nikhil_1486 hi

yes

that was the idea

and I could get using almost the same formula only 193300 or 14723 on january - it failed when I divided it (with or without or with only one variablemember)

deb

N1kh1l
Active Contributor
0 Kudos
Deb_
Participant
0 Kudos

how do I close it?

N1kh1l
Active Contributor
0 Kudos

dd5

You can close it by Accepting the answer if you find it right.

Nikhil

Accepted Solutions (1)

Accepted Solutions (1)

N1kh1l
Active Contributor
dd5

There is a little problem with your code. When you use VARIABLEMEMBER and aggregate the value you need to provide a member for each dimension to get the right calculation. I am running this for the Budget version assigned as prompt in my Data Action

MEMBERSET [d/Date]= "202201" TO "202212" //Adjust accordingly
VARIABLEMEMBER #TOTAL_INCOME_NET OF [d/Account]
VARIABLEMEMBER #TOTAL_QTY_BTL OF [d/Account]
VARIABLEMEMBER #DATE OF [d/Date]

DATA([d/Account] = #TOTAL_INCOME_NET, [d/Date] = #DATE) = RESULTLOOKUP([d/Account] = "Income")

DATA([d/Account] = #TOTAL_QTY_BTL, [d/Date] = #DATE) = RESULTLOOKUP([d/Account] = "Quantity")

DATA([d/Account] = "AVRG_YEARLY_PRICE", [d/Date] = FIRST()) = RESULTLOOKUP([d/Account] = #TOTAL_INCOME_NET, [d/Date] = #DATE) / RESULTLOOKUP([d/Account] = #TOTAL_QTY_BTL, [d/Date] = #DATE)

Output is as expected.

Please upvote and Accept if it helps you.

Regards

Nikhil

Deb_
Participant
0 Kudos

Thank you nikhil_1486

as always, you have an answer to everything 🙂
I'm gonna try 🙂

Deb

Answers (0)