3 Replies Latest reply: Mar 31, 2016 12:46 PM by Aaron Kelly RSS

    Table dimensions as ranges

    Aaron Kelly

      I have a field that contains a range of numbers from 0 well into the hundreds. I'd like to generate a table that looks something like this (the data is oversimplified for the purposes of this example):

       

      Call CountCumulative Call Count% of Total CallsCumulative % of Total Calls
      00:00 to 00:105555
      00:11 to 00:2010151015
      00:21 to 00:30520520
      00:31 to 00:4015351535
      00:41 to 00:5020552055
      00:51 to 00:6040954095
      01:00 to ∞51005100

       

      I can calculate the single row totals and the cumulative totals for each instance but what I can't figure out is how to have a dimension with custom data such as above. Can this be done?

        • Re: Table dimensions as ranges
          Stefan Wühl

          You are talking about how to create buckets with one bucket including everything above a threshold?

           

          Like

           

          =If( Field > 20, Dual('More than 20',20), Class(Field, 5) )

           

          as calculated dimension

          • Re: Table dimensions as ranges
            Sunny Talwar

            Sample Script:

             

            Table:

            LOAD Interval((Ceil(NORMINV(Rand(), 1000, 8)) - 960)/(24*60*60), 'mm:ss') as Call 

            AutoGenerate 100000;

             

            Straight table

             

            Dimension:

            =If(Call <= (10/(24*60*60)), Dual('00:00 - 00:10', 1),

            If(Call <= (20/(24*60*60)), Dual('00:11 - 00:20', 2),

            If(Call <= (30/(24*60*60)), Dual('00:21 - 00:30', 3),

            If(Call <= (40/(24*60*60)), Dual('00:31 - 00:40', 4),

            If(Call <= (50/(24*60*60)), Dual('00:41 - 00:50', 5),

            If(Call <= (60/(24*60*60)), Dual('00:51 - 00:60', 6), Dual('01:00 to ∞', 7)))))))

             

            Expressions

            1) =Count(Call)

            2) =RangeSum(Above(Count(Call), 0, RowNo()))

            3) =Count(Call)/Count(TOTAL Call)

            4) =RangeSum(Above(Count(Call), 0, RowNo()))/Count(TOTAL Call)

             

            Capture.PNG