on 09-26-2013 9:58 AM
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
Modified the data model and implemented the same. Nothing was yielding proper result
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 :
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...
Yes you can get the count based upon various Logical conditions in Bex.
You can get month using formula variable referring characteristics (various dates such as delivery date etc.) giving necessary offset in FV definition right.
Thansk
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
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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...
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
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.
User | Count |
---|---|
61 | |
9 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.