3 Replies Latest reply: Oct 9, 2012 7:27 PM by Stefan Wühl RSS

    Top 10 Rank and Dimension Limits?

    Alexis Hadjisoteriou

      Hi

       

      Let's suppose that I have a measure called Orders.Diff and a dimension Client

       

      I wish to create a bar graph (and a straight table) that meets the following requirements:

       

      a) Top N Clients by sum(Orders.Diff) where N is stored in a variable vTopDiffs

      b) sum(Orders.Diff) < vDiffThreshold

       

      The requirement in a) was easily achieved with:

       

      Calculated Dimension:   =aggr(if(rank(sum(Orders.Diff)) <=vTopDiffs, Client), Client)

      Expression:                  = sum(Orders.Diff)

       

      Any ideas how to achieve b)

       

      Simply replacing

      = sum(Orders.Diff)

      with

      = if(sum(Orders.Diff) < vDiffThreshold, sum(Orders.Diff))

       

      does not work. I suspect the above needs to be added to the Calculated dimension but I have no idea how to do that. Any ideas?

       

      Thanks in advance

      Alexis

        • Re: Top 10 Rank and Dimension Limits?
          Stefan Wühl

          Maybe like

           

          =aggr(if(rank( if(sum(Orders.Diff) < vDiffThreshold, sum(Orders.Diff)) ) <=vTopDiffs, Client), Client)

           

          If using QV11, you could also look into dimension limits:

          Use as first expression something like

           

          =sum({<Client={"=sum(Orders.Diff)<vDiffThreshold"}>}Orders.Diff)

           

          as dimension just Clients and for this dimension, enable 'Restrict which values ...' with option 'show only' with 'Largest' vTopDiffs Value

          on dimension limits tab.

           

          Hope this helps,

          Stefan