Last invoice date in bex

[Former Member]

Hi

I would like to have last invoice date in bex in which it  should not show date in report level i had done exception aggregation last value and ref char calday in report level it is showing total value when i am not taking cal day in rows but i wont only last day result without keeping cal day in rows can any one suggest me any idea on this.

[Former Member · Accepted solution]

Thanks anindhya

could you tell me how to make condition active

[anindya_bose · Accepted solution]

Follow the steps,( not sure if you tried what I said in my previous reply )

Last screenshot shows you result with or without the condition.  You can always deactivate a condition by right clicking on BEx Key Figure--->Toggle Condition Active/Inactive.

Hope this helps.

Cheers

Anindya

[Former Member · Accepted solution]

Hi Anindya

Thanks a lot from your suggestions issue is resolved.

[Former Member]

Can you tell me why to use this

2.  Create a formula as NODIM (  '99991231'  - FORMULA_VAR )

actually i had done

1.rows material

2.i kept kf  new formula and used  in aggregation tab last value ref char calday and in calcluation tab last value

3. result what i am getting is

mat a   dt 30-12-2015      1 (kf)

mat a   dt 29-01-2016       2(kf)

                                       result  2 .

i am getting the result but in out put it is showing all the values but i want only last date result.

in output i  require only

mat a  dt 29-12-2016  2

please guide me in this way and please give me clear explanation .

in rows i am having material ,division

[anindya_bose]

Hi

Basic idea here is to convert date to Key Figure , because Condition can be applied on Key Figure only.

Your Formula can be

Formula_KF =  NODIM (  '99991231'  - FORMULA_VAR )

Or it can be simply      Formula_KF = NODIM(FORMULA_VARIABLE) .

Now if you create a condition on the Formula_KF  to see TOP 1 records, then according to your navigation state, it would select Top record.

Two things to check: 

Do you have different values in division for the same material ? 

Did you do "Toggle Condition Active"  after running the query ?

Cheers

Anindya

[Former Member]

Thanks anindya

I am having material in rows it shouls show last value for each material can you please suggest me in this way

[anindya_bose]

First option should work in that case.

For every material in rows it would select Top value of Formula ( key Figure ) .

Cheers

Anindya

[anindya_bose]

Try this and share the result .

1. Create Formula variable on Invoice Date field with replacement path. Take Key of the InfoObject ( Invoice Date )

2.  Create a formula as NODIM (  '99991231'  - FORMULA_VAR )

3. Create condition on this new Formula . to take TOP N   where N = 1.

Else, write a Customer Exit variable . Which would select distinct dates from your Provider and select only the latest date and then filter with that date in Characteristics restriction.

Cheers

Anindya