opening month calculation

[Former Member]

Hi Experts,

In the columns of Query

calander year/month with selection mandatary variable

structure of

-in(+)

-out(-)

-closing month

-turnover rate/month

(formula)turover rate per month =NDIV0 ( ( 'IN (+)' - 'OUT (-)' )%A ' CLOSING MONTH')

This current formula does NOT meet the full requirements of MY CLIENT.

Based on the client requirements, I should have the following formula :

( IN – OUT ) %A

Hence, in the current formula, we miss the ‘Opening Month’.

Definition of the Closing Month.

o Number of Headcount at the end of the Month.

Definition of the Opening Month.

o Number of Headcount at the beginning of the Month.

 in other words, the OPENING MONTH value is exactly the CLOSING MONTH value of the preceeding month.

 Hence, we should be able to build a « Selection », same as the current Closing Month

closing month

-key figure with head count

-action type with not assigned

-employee status with active and inacive restrictions

But, with added by a complementary characteristics :

 0CAL/Year/MONTH, having a special variable with Offset -1.

This variable should take the value of the main variable over (from Calendar Month/Year) ;

calander year/month with selection mandatary.

And used it as value, that select the previous month, thanks to its ‘offset – 1 ‘.

 please analyze this issue, and propose a solution for applying the cleintrequirement in the formula ?

thanks in advance.

With regards,

MB S

[Former Member · Accepted solution]

It's much easier: Opening Month is Closing Month - In + Out. So your formula transforms to

( IN – OUT ) %A (Closing Month - In + Out + Closing Month) / 2 = ( IN – OUT ) %A (Closing Month - ( In - Out ) / 2 )

Best regards

Dirk

[Former Member]

thanks

[Former Member]

If you expand the calmonth in the Columns, the value of a single column can be seen as a filter and this filter overrides the filters in the structure elements below, and so does the restriction in your variable.

You have to create two restricted keyfigures, one for actual and one for previous month (with respect to the same variable) in the structure and delete the restriction on the free characteristic in the query.

Regards Christoph Vortkamp