6 Replies Latest reply: Oct 27, 2011 11:32 AM by Kiran Rokkam

# Use aggregated value as dimension

Hi,

again I have a strange problem. Unfortunately I can't upload an example but I will do my very best to explain:

There is a logistic related dashboard which counts tours depending on different dimensions. One diagram counts tours per dropoff-place (for example a shop) the table then looks like

Shop
Tours
Aldi Munich499
Lidl Munich320
Rewe Hamburg287
Aldi Berlin154
Aunt Emma Munich90
something differentx

A tour is counted with this formula:

count(distinct if(not ISnull(Frachtpositionen.DBPosID) and trim(Frachtpositionen.DBPosID)<>'',Frachtpositionen.DBPosID,

if(isnull(Auftragsblatt.VKReferenznummer) or trim(Auftragsblatt.VKReferenznummer) = '' ,Auftragsblatt.Kostentraeger)))

So I have different ways of counting across several tables.

The point here is that one tour can have visited several shops. So a truck delivers stuff e.g. to Aldi Munich AND Lidl Munich, the tours is counted in that case for both of the shops. Some tours though deliver to only ONE shops or even 2 or 3 or whatever.

What we now need is this:

ShopsTours
1520
2364
3499
> 31.546

(the counts are just examples, not related to the table above)

We want to know how many tours have visited 1, 2, 3 or more than 3 shops. This means that we need to use a aggregation formula as our dimension. We tried something like

=aggr(count(shops.shopname), v_Tourcounter) but this won't work.

Any ideas on that? I know you get a tie in the brain but perhaps i was able to explain more or less understandable.

Thanks,

Martin

• ###### Use aggregated value as dimension

I think that using something like

=aggr(sum(v_Tourcounter), if(count(shops.shopname) < 3, count(shops.shopname), '>3'))

as my dimension is the right direction but it causes the message "Error in dynamic dimension".

How can I change this?

Thanks.

• ###### Re: Use aggregated value as dimension

Hi,

I sucessfully recreated your problem and solved it in the attachment. Is this what you are looking for?

Regards,

Kiran

• ###### Use aggregated value as dimension

Hi,

Unfortunately not, since I do not have something like a TourID. A Tour is not represented by a record but a count of different records, depending on clauses (as written above). So I don't have a "real" dimension for counting and aggregating... actually the following would be my dimension:

if(

count(distinct

if(not ISnull(Frachtpositionen.DBPosID) and trim(Frachtpositionen.DBPosID)<>'',Frachtpositionen.DBPosID, if(isnull(Auftragsblatt.VKReferenznummer) or trim(Auftragsblatt.VKReferenznummer) = '' ,Auftragsblatt.Kostentraeger))

)

<= 3,

count(distinct

if(not ISnull(Frachtpositionen.DBPosID) and trim(Frachtpositionen.DBPosID)<>'',Frachtpositionen.DBPosID, if(isnull(Auftragsblatt.VKReferenznummer) or trim(Auftragsblatt.VKReferenznummer) = '' ,Auftragsblatt.Kostentraeger))

)

,

'>3'

)

and i think this would have to be subaggregated by the shop which is defined another column Kontokorrent_Kunde.USER_RWMMatchcode2 (<- defines the visited shop).

• ###### Use aggregated value as dimension

You can sub aggr each of the tours and achieve this althought its not the recommened way. The best way is to do this at the script level for both shops and tours and apply the above logic. Aggr function is a heavy function in terms of memory.

Kiran.

• ###### Use aggregated value as dimension

Do you perhaps have a simple example how to use an aggregation as a dimension?

• ###### Re: Use aggregated value as dimension

Check out the attachment.

Kiran.