topPercent function

[praks]

Can somebody please explain how to use the function ? User guide doesn't say much except the syntax.
Thanks in advance.

[billylodz]

topPercent([Product Group],10,[Net Sales])

[LOUM]

How do you list this in you list on a crosstab? I have added it to my query but it still pulls the same results.

[RobsWalker68]

Hi,

Have a read of the Cognos 8 Dimensional Functions document.  This gives a bit more detail with examples of what the function is designed to do.

http://www.ibm.com/developerworks/data/library/cognos/page82.html

Rgds


Rob

[LOUM]

Thank you for the link. I wnet through the process but it doesnt seem to filter my results on the top percentage. I still get all records iin my crosstab. Any ideas?

[cogusergirl]

try substituting in topcount to ensure it's working ok

topcount is easier to debug

as % may not be falling into range you want - have you displayed the % calc on the crosstab?

[LOUM]

Hi cogusergirl,

I tried adding it to the crosstab (CT) but it erros out on my and wont run the CT.
I have changed it to a Top Count  and I am getting the same error when I try to add it to the report.

Also, it does not filter the results as needed.


my statement looks like this in the data items;
topcount ([Commodity],1,[PDRCQT - Quantity Received])

The error is:
Invalid coercion from 'string' to 'memberSet' for '[Commodity]' in 'topcount([Commodity],1,[PDRCQT - Quantity Received])'

Lou

[RobsWalker68]

Hi Lou,

Just to confirm that you are using a dimensional data source such as a cube or DMR and not a relational data source.

If it is dimensional would you be able to provide a bit more information on what the data item "Commodity" is.

Many thanks

Rob 

[LOUM]

Rob,

It is a cube, the commodity is a six digit numeric code.

I am trying to get the top ten percent of my items based of the quantity purchased by commodity.

Setup looks like this:

Commodity Code:     Item Code:     Qty:
999888                     000100         50
999888                     000101         4
999888                     000102         15
777444                     102100         33
777444                     102101         27
777444                     102102         95
777444                     102103         152

Hope this helps.
Lou

[MFGF]

What exactly is Commodity in terms of the dimensional structure, though? Is it a level of a hierarchy? Is it an attribute of a level? Is it a defined set of members?

The TopCount (and TopPercent) functions require a set of members (or a level) to be used as the first argument, and the function then orders the members in the set and returns the top n based on the count or percentage you specify.

It sounds to me like Commodity might be an attribute? If so, this explains your coercion error - it is not legal (or even logical) to use a text attribute here - you need to get the top n % from a collection (set) of members.

Cheers!

MF.

[LOUM]

Hi MFGF!

It is a level in the hierarchy that is higher than the item. I cannot get more techincal, my apologies.

[MFGF]

Hi,

Your expression should look like this, in that case:

topPercent ([Your Package].[Your Dimension].[Your Hierarchy].[Your Commodity Level],10,[Your Qty Measure])

I just tried this on a local dimensional package, and it returns expected results.

Your list (or crosstab) which currently uses the Commodity Level as an item should instead use a Query Calculation coded with the above expression.

Hope that helps!

MF.