cancel
Showing results for 
Search instead for 
Did you mean: 

AR Bex Query

Former Member
0 Kudos

Dear All,

I have configured infocube 0FIAR_C03  to would like get the below KPI

((Number of customer overdue (>30 days) invoces  / Total number of ope invoices) * 100)

I have created a Formula variable on 0Netduedate & used standard 0F_KEYDT to get the current system date.

0F_KEYDT - 0NETDUEDATE  gives me the diff in days.

How do I get the count of invoice which are more than 30 days kindly advise.

Thanks a lot

Accepted Solutions (1)

Accepted Solutions (1)

yasemin_kilinc
Active Contributor
0 Kudos

Hi Kris,

Create a CKF, select "counter for all detailed values" for exception aggregation using Invoice as the reference characteristic. Then create a restricted key figure where you add this key figure and add 0netduedate as less than or equal to a variable that shows today (such as 0VDAYCD). Set offset for this variable as -30. It will solve the issue.

Hope it helps

Regards

Yasemin...

Former Member
0 Kudos

Hi  Yasemin,

Thanks for your reply.

I have managed to get the open invoices as suggested by you. And created buckets. 0 -30 etc.

Now the issue is with cleared documents

Netduedate - cleared date      Diff in days

01.01.2013     02.01.2012       1

now how do we get the count of invoices which are cleared on time or not based on th diff in days

Diff is days <=0 is on time >0 is late.

Kindly advise.

yasemin_kilinc
Active Contributor
0 Kudos

Hi Kris,

From your first post I see that you have already calculated diff in days in your query. If this is so, then, you can create a formula using the CKF you have defined to count the invoices. You can write such a formula to find the number of invoices on time:

(Diff in days <= 0) * CKF(number of invoices)

That should work.

Hope it helps

Regards

Yasemin...

Former Member
0 Kudos

Thanks for your reply,

I am able to get the Open and cleared invoices in a given
month.

Attached image

I have key field 0NETTAKEN which shows the days taken for
the closed invoices

I want the count of 0NETTAKEN records which are <=0

Below is what I have done and it is not given any value

Attached Image

yasemin_kilinc
Active Contributor
0 Kudos

Hi Kris,

Try to use NODIM for NETTAKEN such as

((NODIM(0NETTAKEN)) <= 0) * ZBO_ZFIAR_RK002

Hope it helps

Yasemin...

Sorry for the inconvinience. I changed the formula to correct with the brackets.

Former Member
0 Kudos

HI

Unfortunately it is not working. There is a field 0NETTAKEN in cube 0FIAR_C03. It has values 10,20, -3 , 0 etc.

I want a count of the docs which are <=0 please provide the steps .

I have created a CKF which  shows the count next steps pls.

Thanks

yasemin_kilinc
Active Contributor
0 Kudos

Hi Kris,

Now I get the reason, why you can't see any result. It gets the total of the NETTAKENs for all documents for a month so if the total sum is greater than 0, it is normal you don't see. It would only work when you add document characteristic to your query. Otherwise I can suggest you to add a characteristic to your query where you set it as # or X acording to the value of NETTAKEN being >= 0.

Then you can create a RKF using this characteristic and CKF.

Hope it gives an idea. I will share if any better idea comes to my mind. This is all for now

Regards

Yasemin...

yasemin_kilinc
Active Contributor
0 Kudos

Hi again Kris,

One more idea that comes to my mind is; you can try to create condition selecting 0nettaken greater or equal to zero, but in the characteristic assignment only select the document characteristic. I am not definitely sure that it will work, at least you may give it a try.

Regards

Yasemin...

Answers (0)