Filtering Aggregate Results 3

[Jembo]

I have a report that displays 3 columns (at the momnent);
* NAME - the name of the customer
* CAPTION_ID - the unique number for the name
* LISTING_ID - the unique number for each record belonging to the
NAME/CAPTION_ID.

Each NAME/CAPTION_ID can have several LISTING_ID's.

I have a break on NAME, and a Count on the LISTING_ID column, so I get the
number of LISTING_ID's per NAME.

I only want to see records where the Count (of LISTING_ID's) = 1.
I have tried 'defining a variable' and formatting filters but I can't do it
on a measure (get an error message relating to aggregates).
Any ideas?

Merry Christmas.

 

[skrandel]

You can't filter against aggregated results.

But, there is a cool trick you can use.

Build a variable call it "flag"
=if Count(<listing id>)=1 then 1 else 0

Now pick a unique dimension on the report and apply a ranking to that dimension using your flag variable as the measure you are ranking against. Select the top 1 items and you'll get all the 1s and not the 0s.

Steve Krandel
VERITAS Software

 

[Jembo]

Thank you Steve.
Got this to work to get the base data. Now I can fine tune.
Cheers.

Jeremy

 

[DrSmyth]

Just tried your tip on a couple of old reports that i was having problems with... Not being able to filter aggregates meant that I was constantly having to feed reports through excel in order to give end users what they were after... Not any more though, this works like a dream... Definitely worth a star...

Andy

 

[Leohere]

I've got a similar agregate situation that I'm hoping you might help me with.

I have an order report listing Customer #, Order Count, Gross Units, Product Dollars and Order Source.

Order Count is an agregate total of each order by customer and Ordering Source. The Product Value is an agregate based on the agregated Order Count by Order Source.

I'm trying to filter the agregate result in my Product Value column to display only customers that have ordered >=10000. When I place a filter condition on it, it removes all invividual order values under that were part of the agregate. Any ideas ?

 

[blom0344]

Yes, a filter in BO is aimed directly at the underlying dataset (and NOT at the aggregate result within a table)
I would not be surprised if this is the most disappointing issue with most users

Note: And it is one of very difficult things to explain to my report-writers

Ties Blom
Information analyst

 

[skrandel]

You should be able to use the ranking trick we describe here just fine.

Steve Krandel
Symantec