4 Replies Latest reply: Apr 25, 2017 8:21 AM by Travis Lyon RSS

    Aggr Function Grain Mismatch

    Travis Lyon

      Hello,

       

      I'm trying to fix what I believe is a grain mismatch in an aggr function.

      i have a chart with 2 dimensions, and as labels I'm trying to include

           the value (count of transactions)

            same value as a % of the primary dimension's count of transactions

       

      I'm using a dual() function, but struggling to get the % to display.

      More specifically, I'm having a hard time getting the denominator (count of transactions across my primary dimension)

       

      I'm using the formula

      sum(aggr(DISTINCT Count({<[qBV.Restriction Type]-={''}>}[qBV.Case Number]), [qBV.Primary Payer]))

      where [qBV.Primary Payer] is the primary dimension. The secondary Dim is [qBV.Restriction Type].

       

      But due to the grain mismatch, it's only displaying the percentage on one column.

      Does anyone know how to adjust my work so that the denominator is calculated for each column?

      I'm probably overlooking something simple, but I've tried several different versions of the expression with no success.

      Your help is much appreciated!

       

      Capture.PNG

        • Re: Aggr Function Grain Mismatch
          Jonathan Dienst

          If you do not have all the chart dimensions in the Aggr() function, it does not produce output for the all the chart columns, so you will only get results in one column.

           

          You need to add both chart dimensions to the Aggr(). This could change the output, so you may need to add a partial total back to correct this.

           

          BYW I see a DISTINCT for your Sum. Should the DISTINCT not be for the Count?

            • Re: Aggr Function Grain Mismatch
              Travis Lyon

              Thanks for the response!

               

              Good eye... The DISTINCT was just a left over from my testing; i forgot to get it out of there before posting the expression.

               

              Do you happen to have any suggestions on how to add the partial total back?

              Sorry, I'm just not understanding how to get it to work (without hard coding the values of my second dimension into the expression's set analysis).


              The two legend values shown aren't the only possible values for the dimension. I could probably get away with making them that way, but I'd really like the chart to be more flexible.

               

              When I add both dimensions to my chart, it just returns the values of the individual bars, making my %s = 100%

               

              Thanks again!