If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Rank and filter on % of total

Started by LOUM, 09 Jul 2012 11:05:05 AM

Previous topic - Next topic

LOUM

Good Day Everyone.

I have a list report that I need assistance on.

The list pulls all my items from our warehouse and counts them. It also quantifes the usage and we assign a rank to the records.

I am then trying to add a filter to only return the percentage based on a filter.

see my example below.

Rank: Itemcode:   Qty:
1         111111       100
2          222222       99
3          333333       98
4          444444       95
5          555555       85
6          666666       70
and so on.

I have 797 items in stock, My criteria is 20% which would be 159 items.

I want to have a filter that takes the 159 figure and then grabs the ranked items that are 159 and under as the result for the list.

Help.

Thank you.
Lou


LOUM

Wow did I stump everyone or did I just not give enough info on the question?

???

blom0344

You may need to define a running-total based on the rank order and use the correct filter on it. I suppose this would be a summary filter though I have no idea if it will work against a cumulative calculation..

MFGF

Am I interpreting this correctly? You need to rank the results based on the Qty column, count the total number of ranked summary rows returned, then filter where the rank value is less than 20% of the total number of rows?

I managed this using the GO Sales (query) sample package. I added a rank calc first of all - rank([Qty] for report), then I added a Count calc - count([Itemcode] for report), then finally I added a detail filter (after aggregation) with the expression [Rank calc]/[Count calc] <= 0.2

Is this what you wanted?

MF.
Meep!

LOUM

Hi all ty for the reponses:

My sequence of events is as follows.

Data item 1 = My inventory Ranked by QTY.
Data item 2 = My total item number(797) divided by a percentage entered on the prompt page, 20% in this instance returning the result of 159.

I would like a filter that after the ranking and item numbers percentages has run would bring back only the Ranked items up to the percentage of inventory number, 159 in this example.

So instead of bringing back all 797 ranked items it only brings back 1 through 159.

Thank you for your tiime and assistance.
Lou

MFGF

That looks remarkably like the example I provided, with the exception of the percentage being a parameter from the prompt rather than a hard-coded value. Did you try it?

MF.

Sent from my iPad using Tapatalk HD
Meep!

LOUM

M:

That is how I have it written. It comes back blank when I filter the Rank <= DataItem2.

LOUM

Solved.

TY All for the assistance!!!

;D

Lou