Skip to Content
-1

BPC Member Formula

Mar 02 at 12:46 PM

55

avatar image
Former Member

Hi All,

I am trying to create a Member formula to check the average of Q1,Q2,Q3,Q4 of the current year and also prior year Q4 when reported on TOTAL year. How can I get the sum of all 4 quarters of a year. I tried the following code but this statement is not giving any results. Can anyone suggest what needs to be done?

Average Account=IIF(TIME.CURRENTMEMBER.PROPERTIES("LEVEL") = “YEAR”,sum(([TIME].H1.currentmember.children,[ACCOUNT].[A230000]), ([TIME].CURRENTMEMBER.PREVMEMBER,[ACCOUNT].[A230000]))/2,0)

Any help is much appreciated,

Regards,

Sailu

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
Vadim Kalinin Mar 02 at 10:14 PM
1

First - NEVER use member ID with space inside! Not "TEST ACCOUNT" but "TEST_ACCOUNT" or something like!

Correct Formula:

IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="YEAR",(SUM([TIME].CURRENTMEMBER.CHILDREN,[A230000])+([TIME].CURRENTMEMBER.LAG(1),[A230000]))/5,NULL)

Last quarter of the previous year will be equal to prev year TOTAL: [TIME].CURRENTMEMBER.LAG(1)

Test report:


df1.png (27.4 kB)
Share
10 |10000 characters needed characters left characters exceeded
Vadim Kalinin Mar 02 at 12:55 PM
0

"I am trying to create a Member formula to check the average of Q1,Q2,Q3,Q4 of the current year and also prior year Q4 when reported on TOTAL year" - sorry, but absolutely not clear what do you want to achieve!

Please spend some time to provide a clear explanation with a sample screenshot.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Mar 02 at 09:17 PM
0

Hi Vadim,

Thanks for the quick response and apologies for not giving enough details.

In the member formula when the currentmember is a TOTAL node, I want to calculate average of 5 quarters going back which is 4 quarters of 2017 and last quarter Q4 of 2016.

I appreciate any help.

-Sailu


memberformula.png (15.3 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded

And if it's not TOTAL?

0
avatar image
Former Member Mar 02 at 11:42 PM
0

It worked...Thanks very much.

Share
10 |10000 characters needed characters left characters exceeded