Not part of Aggregate/Can't have aggragate

[Phil Smith]

I ran into something like this before with this same dataset, got help
here from John Spencer, but can't seem to translate that solution to
this problem.

I have a crosstab. I am trying in this case to "split" the crosstab so
as to get more then one value. I added a two record table unjoined.

Here is my "value" formula:


IIf([Counter]![Counter]=1,Min(Format([ItemsOnSelectedPrebook-Type%]![Perc],"Percent")),[Items
on Prebook-Type Brand-Count]![CountOfold_sku])

As is, it errors out with "Not part of an aggregate function."

So I make an aggregate function like so:
sum(IIf([Counter]![Counter]=1,Min(Format([ItemsOnSelectedPrebook-Type%]![Perc],"Percent")),[Items
on Prebook-Type Brand-Count]![CountOfold_sku]))
I get "Cannot have aggregate function."

Damned if I do, damned if I don't. Anyone got a "get out of hell free"
card?

Phil

 

[Duane Hookom]

How about something like:

IIf([Counter]![Counter]=1,
Min(Format([ItemsOnSelectedPrebook-Type%]![Perc],"Percent")),
First([Items on Prebook-Type Brand-Count]![CountOfold_sku]))

BTW: I'm not sure why you are formatting in the expession. I generally leave
all formatting to controls in forms and reports.

 

[Phil Smith]

sum(IIf([Counter]![Counter]=1,([ItemsOnSelectedPrebook-Type%]![Perc]),[Items
on Prebook-Type Brand-Count]![CountOfold_sku]))

Did the trick. I had to tweak the underlying query, and I had to remove
the formatting.

The reason I was formatting it in the query was this is a straight to
Excel query. I am going to have to do some other formatting after it
gets exported to Excel anyway, so I could get rid of it.

Thanx