Measure HYTD - negative figures

[Former Member]

Hi everyone,

We implemented the measure HYTD by adding the members in the TIME dimension with the properties as the others members.

The level is HALFYEAR.

After processing, we can see in BPC excel the measure HYTD, it seems ok.

Now if we use it on all month on a year ...

On periodic ... we can see the figures (aggregation on all other dimensions

On QTD, ... we can see january in january, jan + feb in feb, jan + feb + mar in march, april in april ... so it is correct

On YTD ... we see each month the aggregation of the months before

And on HYTD ... the system uses the same method than for quarter (but on 6 months of course) ... but with negative numbers on the screen !

Did already someone had this issue ?

Thank you very much

[Former Member · Accepted solution]

Hi Eric,

How have you implemented the HYTD measure? Have you taken the acctype into consideration while maintaining this measure? You can refer to the other measures for more information. Any INC or LEQ type accounts are stored as negative values and any EXP or AST type accounts are stored as positive values. So, please take this into consideration.

Hope this helps.

[Former Member · Accepted solution]

This is an application for technical indicators.

All accounts are EXP or AST (only one is AST)

All my calculations in QTD or YTD are correct, this is only the HYTD that is negative.

To create the measure, we just added into the TIME dimensions the members for semeters.

Here is one

ID : 2005.HY1

EV Description : 2005 HY1

Parent H1 : 2005.TOTAL

Year : 2005

Period : HY1

Level : HALFYEAR

TimeID : 10000003 (built automatically on process, right ?)

HLEVEL : 2

MONTHNUM : 6

Of course, Quarters are now at level 3 and the parent of Q1, Q2 is HY1 and parents of Q3, Q4 is HY2

[Former Member · Accepted solution]

Hi Eric,

So, that means that you have not created any measure. Its just that you have added another member in the time level and you want the values to be aggregated over there. Is my understanding correct?

The aggregation depends on the ISBEGINNING property. Please check whether it has been maintained properly or not.

ISBEGINNING of HY1 should be 1 and of HY2 should be 0.

Hope this helps.

[Former Member · Accepted solution]

Little information more ... the only account in AST is correctly calculated in HYTD with positive figure. Only EXP is done by this way.

As you understood, the measure has been built automatically by BPC, can we imagine that there is a bug in the configuration of the built measure and the considered EXP as negative numbers ?

[Former Member · Accepted solution]

Right, I did not create any measure, BPC created it automatically in the dimension.

None of the members of my TIME dimension has this property. Everything is empty.

What rule should I use ?

0 when ... ?

1 when ... ?

[Former Member · Accepted solution]

Hi Eric,

TOTAL = 1

HY1 = 1

Q1 = 1

JAN = 1

FEB = 0

MAR = 0

Q2 = 0

APR = 0

MAY = 0

JUN = 0

HY2 = 0

Q3 = 0

JUL = 0

AUG = 0

SEP = 0

Q4 = 0

OCT = 0

NOV = 0

DEC = 0

Maintain it accordingly. and try. If it still doesnt solve, then you need to look at the measure for HYTD. I think there might be a problem too.

Hope this helps.

[PatrickFavre · Accepted solution]

Hi,

Nilajan is right.

The rule is the following:

Set "1" in ISBEGINNING propertyfor starting period Time members (Total, H1, Q1, and Jan).

Then, if you have a look to MeasureFormula table in SQL back-end, you'll see that ISEBEGINNING property is used for YTD applications.

Hope this will help.

Kind Regards,

Patrick

[Former Member · Accepted solution]

Unfortunately, I still have negative values, except on AST accounts.

Does it mean that we have to check in the definition of the measure in the DB what is used to define positive and negative ? Maybe the better is to open an incident at SAP support level ?

Thank you very much

[Former Member · Accepted solution]

Hi Eric,

Yes your are right. If the maintenance of ISBEGINNING didnt solve your problem, then please go to the measureformula table in SQL and see how the formula has been maintained. I guess you need to have a factor of -1 to be multiplied, if the ACCTYPE is EXP or AST. Please refer to the periodic measure.

Hope this helps.

[Former Member · Accepted solution]

Could you please have a look and confirm my idea ? I do not know the MDX, so I interpret :

Here is Periodic (I breadk voluntary the line to show what I want to show

IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="INC",

-MEASURES.[SIGNEDDATA],

IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="EXP",

MEASURES.[SIGNEDDATA],

IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="AST",

(MEASURES.[SIGNEDDATA],CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%])),

IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="LEQ",

-(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%])),-MEASURES.[SIGNEDDATA]))))

We can see that when we call MEASURES.[SIGNEDDATA] for INC and for LEQ, we have the sign MINUS

Here is now HYTD

IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="INC",

SUM(PERIODSTODATE([%TIMEDIM%].HALFYEAR,[%TIMEDIM%].CURRENTMEMBER),-MEASURES.[SIGNEDDATA]),

IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="EXP",

SUM(PERIODSTODATE([%TIMEDIM%].HALFYEAR,[%TIMEDIM%].CURRENTMEMBER),-MEASURES.[SIGNEDDATA]),

IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="AST",

(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%])),

IIF([%ACCOUNTDIM%].CURRENTMEMBER.PROPERTIES("ACCTYPE")="LEQ",

-(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([%TIMEDIM%].[%TIMEBASELEVEL%])),-MEASURES.[SIGNEDDATA]))))

It is a little bit defined differently than standard measures for QTD, WTD or YTD, but if we have a look to the usage of MEASURES on the different properties, we can see that on EXP, there is the MINUS and not on AST

[Former Member · Accepted solution]

Hi Eric,

As you can see that INC and LEQ are multiplied by a factor of -1. Similarly, you need to have a factor of -1 in your measure formula as well.

Your INC looks fine.

For EXP, use SUM(PERIODSTODATE(%TIMEDIM%.HALFYEAR,%TIMEDIM%.CURRENTMEMBER),MEASURES.SIGNEDDATA)

Your AST is fine.

For LEQ, remove the negative sign from the front of the formula.

Hope this helps.

[Former Member · Accepted solution]

Are you sure about LEQ ?

If I well understood, I must have a minus for LEQ and INC right ? If yes why remove the minus in this case ?

From my understanding, I only have to remove the minus for the EXP ...

But will it stay ? Or will it be removed each time the cube is processed ? Shouldn't I open a case to the support ? That mean that everyone using it will have the issue because the measure have been created automatically ...

Thanks

[Former Member · Accepted solution]

Hi Eric,

I am really sorry, I got confused with the statement

-(MEASURES.SIGNEDDATA, CLOSINGPERIOD(%TIMEDIM%.%TIMEBASELEVEL%)),-MEASURES.SIGNEDDATA))))

However, the statement for LEQ is

-(MEASURES.SIGNEDDATA, CLOSINGPERIOD(%TIMEDIM%.%TIMEBASELEVEL%))

This is absolutely fine.

Hope this helps.