Customer measure: Activity

[former_member449239]

Hi Friends,

I need to develop a custom measure called "Activity".

Data available at periods level.

customer-measure-activity.jpg

Lines 5& 6 are LEQ Acc type with Period & Activity measures.

Similarly lines 9 & 10 are AST Acc type with Period & Activity measures

Need to derive "Activity" measure values for month, quarter & year level. Here values are generated correctly for months & year level. Need help in Quarter level figs derivation. All time periods (i.e.Quarters, months have properties PRIOR) in PERIOD dimension. Here PERIOD is TIME dimension.

2018.Q1 prior value is 2018.Q4

2018.Q2 prior value is 2018.Q1

2018.Q3 prior value is 2018.Q2

2018.Q4 prior value is 2018.Q3

2018.Q1 = 2018.Q1 (Periodic measure) - 2018.Q4 (Periodic measure)

2018 Q2 = 2018.Q2 (Periodic measure) - 2018.Q1 (Periodic measure)

2018 Q3 = 2018.Q3 (Periodic measure) - 2018.Q2 (Periodic measure)

2018 Q4 =2018.Q4 (Periodic measure) - 2018.Q3 (Periodic measure)

For months derivation: Current month (periodic measure value) - previous month(periodic measure value)

I am getting error while reading Quarter values and pls suggest on this.

Ours is BPC 10.1 NW,

BW7.4 on HANA SP13.

I read in blogs STRTOVALUE supported from SP12 onwards.

E.g. Acct type: LEQ OR AST 'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",

IIF([%PERIOD%].CURRENTMEMBER.PROPERTIES("2/CPMB/TILEVEL")="QUARTER", (STRTOMEMBER("[%PERIOD%].[" + [%PERIOD%].CURRENTMEMBER.PROPERTIES("CURRENT") + "]"), [MEASURES].[/CPMB/SDATA] )- ((STRTOMEMBER("[%PERIOD%].[" + [%PERIOD%].CURRENTMEMBER.PROPERTIES("PRIOR") + "]"), [MEASURES].[/CPMB/SDATA]),

-([MEASURES].[/CPMB/SDATA] - ([MEASURES].[/CPMB/SDATA], [%PERIOD%].PrevMember))), -[MEASURES].[/CPMB/SDATA])';SOLVE_ORDER=3

Here require support at bold letters place.

Regards,

Anjan

[former_member186338]

STRTOMEMBER is supported, but concatenation in STRTOMEMBER argument is not supported 🙂

Also try to explain again the required measure logic, your explanation is not clear

[former_member449239]

Here I need help in derivation of Activity measures's Quarter values.

Each quarter has two properties: CURRENT & PRIOR

E.g. 2018.Q1, CURRENT = 2018.Q1

PRIOR = 2018.Q4

2018.Q2, CURRENT= 2018.Q2

PRIOR= 2018.Q1

2018.Q3, CURRENT = 2018.Q3

PRIOR = 2018.Q2

2018.Q4, CURRENT = 2018.Q4,

PRIOR = 2018.Q3

Formula for derivation of quarters:

QUARTER (Activity measure value) = CURRENT (Periodic measure value) - PRIOR (Periodic measure value)

E.g. 2018.Q1 (Activity measure value) = 2018.Q1 (Periodic measure value,which is CURRENT) - 2018.Q4 (Periodic measure value, which is PRIOR)

For line number 6 (Acctype: LEQ)

2018.Q1: 4 - 0 = 4

2018.Q2: 0 - 4 = -4

2018.Q3: 0 - 0 = 0

2018.Q4: 0 - 0 = 0

Thanks.

[former_member186338]

Unable to understand logic:

"E.g. 2018.Q1, CURRENT = 2018.Q1

PRIOR = 2018.Q4" -??????????????? 2018???

Also no explanation about months and years!

Please spend some time to provide full explanation!

[former_member186338]

P.S. Also - what is the model storage type? PERIODIC or YTD?

[former_member449239]

Sorry.

Datastorage is Periodic

Periodic measure shows values as in line number 5. These are all(year,quarter & month levels) fine and stored in database.

Activity measure values(year,quarter,month levels) need to be derived based on above Periodic measure values(i.e. line 6 values need to be derived from line5).

Logic for derivation month (Activity measure): Current month(Periodic measure) - Previous month(Periodic measure) Logic for derivation quarter(Activity measure): Current Qtr(Periodic measure) - Previous Qtr(Periodic measure)

Logic for derivation year(Activity measure): Sum of all base months(Periodic measure)

"Activity" measure (Code) correctly derives year and months level values(i.e. Activity measure) but need help at derivation of quarter values.

Each Quarter has two properties:

1) "CURRENT" which holds exactly current quarter value

2) "PRIOR", which holds prior quarter value.

E.g. 2018.Q1 has two properties and values are

1) CURRENT = 2018.Q1

2) PRIOR= 2018.Q4

2018.Q2 has two properties and values are

1) CURRENT = 2018.Q2

2) PRIOR= 2018.Q1

2018.Q3 has two properties and values are

1) CURRENT = 2018.Q3

2) PRIOR= 2018.Q2

2018.Q4 has two properties and values are

1) CURRENT = 2018.Q4

2) PRIOR= 2018.Q3

Derivation of Quarters :

2018.Q1 (Activity measure) = 2018.Q1 - 2018.Q4

2018.Q2 (Activity measure) = 2018.Q2 - 2018.Q1

2018.Q3 (Activity measure) = 2018.Q3 - 2018.Q2

2018.Q4 (Activity measure) = 2018.Q4 - 2018.Q3

Logic is same for AST and LEQ accounts. Here I am finding difficult in reading Quarter values(Periodic measure)

Thanks..Pls mention if I miss anything.

[former_member186338]

How it's possible:

1) CURRENT = 2018.Q1

2) PRIOR= 2018.Q4

What is the business idea to perform 2018.Q1 - 2018.Q4

2018.Q4 is not a previous quarter for 2018.Q1

May be you are talking about 2017.Q4

[former_member449239]

yes, this is business logic. Even I also asked business idea and I didn't get that.

Pls share any code samples, to read parent members based on properties values. thanks.

[former_member186338]

Sorry, but I do not support logic without common sense!

Ask customer again! Do nothing before you clearly understand the logic - it's not professional 😞