Use aggregated value as dimension

[#1]

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 Munich	499
Lidl Munich	320
Rewe Hamburg	287
Aldi Berlin	154
Aunt Emma Munich	90
something different	x

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:

Shops	Tours
1	520
2	364
3	499
> 3	1.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

[#2]

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.

[#3]

Hi,

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

Regards,

Kiran

[#4]

Hi,

thanks for your help.

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).

[#5]

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.

[#6]

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

[#7]

Check out the attachment.

Kiran.