1 Reply Latest reply: Mar 18, 2013 12:34 PM by Adam Bellerby RSS

    Table Expressions :  Gran Total

      I'm trying to make some calculations to keep the Score for results but I can't get the correct Totals (Grand Total). 

       

      Some background so you know what I'm trying to accomplish:

       

      I have a Static table ( which I control by ignoring selections and by establishing specific criteria)

       

       

      1. I'm displaying results specific to Monthly Type and other conditions listed here:

       

       

      {1<

      [Survey Type] = {'Top Box'}, [Unit] = {'Total'}, OVRL = {'Y'}, PeriodType = {'Monthly'}>}

       

       

      2.  Now I have to show based on the Group(dimension) who met the condition: (I used Only & Avg and get same result which is correct at Domain level

       

       

      =

      if ((Only({1<[Survey Type] = {'Top Box'}, [Unit] = {'Total'}, OVRL = {'Y'}, PeriodType = {'Monthly'}>}[Group %Tile]))>= 50,1,0)

       

       

       

       

      At the Domain Level  I get 1 or 0 correctly when >= 50.  But when trying to get grand total I get Zero (0).  I have 10 Domains and 3 met I should see 3 on my grand Total  but shows Zero.

       

      Originally I was using AVG instead of ONLY and I thought that was my problem but still get same result.

       

       

      If you can share some insight on this will be great.

       

      ZD

        • Re: Table Expressions :  Gran Total
          Adam Bellerby

          Hi without seeing an app, I think you are either......

           

          - missing an aggregation function around you IF Statement...

           

          =SUM(

           

          if ((Only({1<[Survey Type] = {'Top Box'}, [Unit] = {'Total'}, OVRL = {'Y'}, PeriodType = {'Monthly'}>}[Group %Tile]))>= 50,1,0)

          )

           

          Try putting a simple sum.

           

          - Alternatively, in your expression tab of your chart properties, you can try to select sum of rows rather than expression total in the "Total Mode" setting.

           

          Cheers.

           

          ABY.