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

    Use aggregated value as dimension

    Martin Vierling

      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