2 Replies Latest reply: Jan 14, 2013 11:11 AM by Sandro Krumbein RSS

    Sum of Counts...

    Sandro Krumbein

      Hello everyone!

       

      I have another problem and would appreciate any input you guys could give me

       

      We have several subsidiaries, which generate revenue at different hours in a day. The raw table looks something like this:

       

      SubBonNoHourRevDate
      F1111

      01.01.12

      F121201.01.12
      F132301.01.12
      F142401.01.12
      F153501.01.12
      F163601.01.12
      F212101.01.12
      F222201.01.12
      F232302.01.12
      F242402.01.12
      F252503.01.12
      F263603.01.12

       

      I have created a table, which shows revenue per hour per subsidiary and looks like this:

       

      HourSubCountSum of Rev
      1F11

      3

      2F11

      7

      2F2315
      3F1111
      3F216

      Dimensions: Hour, Sub

      Formulas: Count = count(distinct total<Hour,Sub> Datum), Sum of Rev = sum(Rev)

       

      What I would actually like to have is the above table, but with the Sub dimension removed and the count summed additionally over that dimension instead, like this:

      HourCountSum of Rev
      113
      2422
      3217

      This should be Revenue per Subsidiary-Hour (or whatever you want to call it)

       

      Sum of Rev = sum(count(distinct total <Hour,Sub> Datum) doesn't work

       

      I am sure there is a way, but I don't see it right now

       

      Regards,

      Sandro