Condition based COUNT in BEX

[former_member197527]

Hi all,

Have the below req:

Need to calculate the count of orders, which have the order allotment month along with delivery month and inspection month.

1. If for an order, if there is an order allotment month, we should also verify if it has got the inspection and delivery months (Everything in same month). Then we need to give such count. In addition to this, if there is a inspection month available, then we should NOT consider delivery month.

2. For another count, everything is similar except that the order allotment month must be in different month than the inspection and delivery ones.

Where can I achieve such count based on conditions...Pls guide on COUNT

[former_member197527 · Accepted solution]

Modified the data model and implemented the same. Nothing was yielding proper result

[Former Member]

Hi Aditya,

Can you clarify this :

1) in point 1 you said "everything in the same month" ? What does this mean that there is only one info object for month and against each order there are 3 rows for different months

2) In point 2 you different months . Does it mean you have 3 different info objects for months ?

But going by what Yasemin suggested above i would tweak her solution a bit .

Maintain an indicator(which is nothing but a characteristic) at the transformation level which satisfies your conditions mentioned in points 1 & 2 of your post.

Now make count of orders key figure based on the below link :

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/009819ab-c96e-2910-bbb2-c85f7bdec...

Then restrict this keyfigure with your indicators which you maintained at transformation level.

This will give you the unique count of orders based on the condition you mentioned in point 1 & 2 of your post.

Hope the above solution helps !!

Regards,

Ashutosh Singh

[former_member197527]

Hi Ashutosh,

Need to find the count of orders on one condition:

If an order has an allotted date in Dec, and if its delivery date or inspection date falls in Dec ONLY, then we need count of all such orders. This needs to be displayed for EVERY month in report.

We already have a count KF in cube, but its just constant 1 which doesn't have such date comparision based count.

Can we based this on COUNT in bex based on logical conditions? How can I get the month in the formula variable...

[KamalMehta]

Yes you can get the count based upon various Logical conditions in Bex.

Refer http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/d08b56a8-daf5-2e10-2397-904d6aeb5...

You can get month using formula variable referring characteristics (various dates such as delivery date etc.) giving necessary offset in FV definition right.

Thansk

[former_member197527]

Hi Kamal,

Please look at my latest reply...http://scn.sap.com/thread/3429566

Also why does the query designer doesn't accept something as

COUNT() + ( COUNT() X COUNT() )

Even if I give the parenthesis, it doesn't consider and only saves as

COUNT() + COUNT() X COUNT()

Hence am going in a longer way...say A = COUNT() and B = COUNT() X COUNT()

And then creating another formula as A+B

[yasemin_kilinc]

Hi Aditya,

Instead of doing it in query designer you can insert a key figure for each of your requirement to your infoprovder. For example, add a key figure for 1, in the transformation, add a routine or formula to check your requirement. In the query you can simply use this key figure. This will be the easier method than trying in query designer.

Hope it gives an idea

Yasemin...

[former_member197527]

We already have such count.

But thing is, we need to get this count based on the said condition.

My guess was to add some condition in TRFN, but it was not in design.

We now only have an option to do this in query designer

[anshu_lilhori]

Hi,

As you told that you are not flexible with modeling level change.

1. If for an order, if there is an order allotment month, we should also verify if it has got the inspection and delivery months (Everything in same month). Then we need to give such count. In addition to this, if there is a inspection month available, then we should NOT consider delivery month.

Then try with RKF. Try to restrict your count kf with both those months---Drag and drop allotment month--Restrict--#--Exclude.

Do the same procedure now with the inspection month.

This will give you the desired count.Give a try to this process.

Regards,

AL

[former_member197527]

Hi Anshu,

Thanks for replying.

Need to find the count of orders on one condition:

If an order has an allotted date in Dec, and if its delivery date or inspection date falls in Dec ONLY, then we need count of all such orders. This needs to be displayed for EVERY month in report.

Already tried this approach. With an RKF on allotted month restriction for say Jan, we should also have the other month restrictions in same month, this didn't get exact result.

Hence we were asked to proceed with the COUNT function in a formula...

COUNT(allotted month = Dec) + COUNT ((delivery month = Dec) OR (inspection month = Dec)).

Will this give me the exact count...

[anshu_lilhori]

Just clarify one thing will your user will be entering month in any of the month or how would it be.

[former_member197527]

No user inputs...as such

This needs to come for all the months...like below:

Am asking reg one of the KF k1 and k2...

k1 Quat1     k2 Quat1    k1 Jan    k2 Jan   k1 Feb   K2 Feb   k1 Mar   k2 Mar   K1 Quat2   K2 Quat2....

This is fixed report...

[anshu_lilhori]

Ok so now i understand--You have posted same type of requirement in other threads so its all inter related i believe.

Now i assume you will be having this fixed layout report for complete current year without any user input.

So how you are planning to create these Kfs.I mean separate kf for each month or with the help of umbrella concept as mentioned in other threads.

Would you be able to explain the whole thing with data including all months so that i get an idea about the data and output required.

I really need to scratch my head to reach closer to your requirement..

Regards,

AL

[former_member197527]

Am creating separate keyfigures for my req as umbrella doesn't work.

This leads to creation of almost 100 formulas for all months as its a fixed report.

RKF on a count with selection on a month -> The count has to be done for multiple dates and not on a single date. So guess will not work accurately. Please confirm and hence am using month check in conditions.

Please look at my latest reply at http://scn.sap.com/thread/3429566

Help me as I gave clear picture.