Question:
How do I count a characteristic per another characteristic WHERE value of a key figure is greater than a certain amount.
e.g. count number of PO's per purchase org where PO value > $5000
The How-To count paper seems to address the count no of PO's per purch org but doesn't allow for the WHERE PO value > $5000
Conditions don't work since they are a condition after the query has run (ie total PO value per Purch org rather than each individual PO value).
Any guru's out there with an idea?
Answer:
I've never seen it done on BW, but this is how I might approach your challenge.
Create a sub query that aggregates up to the PO level and only includes POs > $5,000. Feed the PO numbers to a super query that is filtered on the PO #. Then you can count and group by whatever you need.
Or, when you load the cube you can make total PO value a characteristic.
Jim F.
Answer:
Thanks for replying Jim
The sub query sounds as though it would be very bad performance given number of records we are likely to have.
The idea of putting the key figure value in a characteristic is intriguing if rather unusual.
Anyone else any bright ideas?
Answer:
Thinking quick, my idea would be:
-Create a characteristic for the PO value, type CHAR, conversion routine NUMCV.
-Add the new characteristic to the cube
-Fill it in the update rules with the key figure value (you may need a routine to do this).
-Select this characteristic in the query (remember it's char, so use intervals)
-Count whatever it comes in the query.
Leandro
Answer:
Read my post below from 1 July. Create a sub query that identifies POs over $5,000 and feed the PO number to the super query, then count by Purchasing org or whatever in the super query.
Jim F.
Answer:
Jim,
I don't like the idea of using a subquery and feeding a list of PO's to a second query. There could be hundreds of thousands or more PO's to be fed to the second query. It sounds as though it will have very poor performance. Have you actually done this, and if so with what volume of data? Thanks.
Answer:
Hi guest - did you manage to find a way to do this (excludign the pre-query which is a very bad idea)? We need to do the same thing.
Thanks.
Answer:
Hi, try using virtual characteristics. This has the particularity that runs after the filter selection of the query has been done, so you can get the exact agregation. For example, use this virtual characteristic as kind of flag if PO is greater that XX then flag is 1, then you also have another Key Figure as a counter and you have it. (just an example)
I think this was the way that I got through counting where....
Regards,
Andrea.