1 Reply Latest reply on Feb 19, 2016 5:39 AM by Jonathan Drummey

    SubTotal and GrandTotal not computing correctly

    Ali Rafique

      Hi,

      I am running into this issue where the sub total and grand total are not showing the correct sum for a calculated field.

      The sub total for region "Amer" region is showing -76,377 where as it should be -77,277.. the incorrect total for some reason is add the USA OTHER and Contra Rev Amer Quota to sub total as well.

      The grand totals are incorrect as well. The "SFDC or LB Bookings" grand total is not taking into account "Amer" region.

      The "SFDC or LB Bookings minus Quota" grand total is for some reason adding "Amer" region" SFDC or LB Bookings" sub total of 34,623 as a negative taking the grand total to -175,000 where as it should be showing -141,277 (-77,277 -64,000)

       

       

      Given below is an example:

      Region: dimension

      Area: dimension

      Current Qtr Quota: Measure

      SFDC or LB Bookings: Calculated field, using this formula: IF [LB Bookings]> 0 THEN [LB Bookings] ELSE SUM([SFDC Bookings) END

      SFDC or LB Bookings minus Quota: Calculated field, using this formula:  [SFDC or LB Bookings]-SUM([Current Qtr Quota])

      Correct SFDC or LB Bookings minus Quota: Calculated field, using this formula: IF ATTR([*Area])="USA OTHER" then 0 ELSEIF ATTR([*Area])="Contra Rev AMER" then 0 ELSE [SFDC or LB Bookings minus Quota] END

      The reason for creating the field was to disregard the -130 and 1,030 for USA OTHER and Contra Rev AMER as these are place holders quotas and there will never be any SFDC or LB Bookings against them hence I don't want to add these to "SFDC or LB Bookings minus Quota".

       

      I would appreciate your help with fixing these issues.

      many thanks

      Ali

       

       

      Jonathan Drummey

      Matt Lutton

       

       

      Region*AreaCurrent Qtr QuotaSFDC or LB
        Bookings
      SFDC or LB
        Bookings minus Quota
      Correct 'SFDC
        or LB Bookings minus Quota
      AmerUS115,0004,050-10,950-10,950
      US222,0007,923-14,077-14,077
      US314,0002,701-11,299-11,299
      US415,0004,442-10,558-10,558
      US53,200730-2,470-2,470
      US611,9003,948-7,952-7,952
      US7250-250-250
      US87,2003,167-4,033-4,033
      US911,8504,586-7,264-7,264
      USA OTHER130-130
      US1011,5003,075-8,425-8,425
      Contra Rev AMER-1,0301,030
      Sub Total111,00034,623-76,377-76,377
      EMEAEMEA129,0009,000-20,000-20,000
      EMEA225,0005,000-20,000-20,000
      EMEA331,0007,000-24,000-24,000
      Sub Total85,00021,000-64,000-64,000
      GrandTotal 196,00021,000-175,000-175,000