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:




      [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.



        • 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...




          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.