15 Replies Latest reply: Jun 14, 2017 7:32 AM by Tom Neal

Straight table calculated total row

Tom Neal Jun 14, 2017 5:04 AM

Hi All,

My requirement is to have a calculated total different to it's expression.

My expression is simply calling a field and I want the total of this column to be a calculation of two previously calculated expressions.

The Yield target is not a calculated so the total is not correct as can be seen as blank below.

I would like the Yield Target total to be Standard / Target so it would be 59616.55 / 58008.90

I should also point out that the standard and target columns are both expressions.

Thanks,

Tom

Re: Straight table calculated total row

Shiva Nagesh Jun 14, 2017 5:07 AM (in response to Tom Neal)

Neal, didn't this work [Standard]/[Target]??
Like Show 0 Likes (0)

Actions
- Re: Straight table calculated total row
  
  Anil Babu Samineni Jun 14, 2017 5:13 AM (in response to Shiva Nagesh)
  
  Should be this for Yield Target
  Sum(TOTAL [Standard]) / Sum(TOTAL [Target])
  
  Like Show 0 Likes (0)
  
  Actions
- Re: Straight table calculated total row
  
  Tom Neal Jun 14, 2017 5:16 AM (in response to Shiva Nagesh)
  
  Unfortunately not, unless I've overlooked something the result I got for Standard/Target is 313% as the Total.
  
  That calculation works for the row values but not the total. The result I should get in the total is 102.7%
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Straight table calculated total row
    
    Anil Babu Samineni Jun 14, 2017 5:27 AM (in response to Tom Neal)
    
    Seems Working?
    Num((Sum(Stadard)/sum(Target)),'#,##0.00%')
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: Straight table calculated total row
      
      Tom Neal Jun 14, 2017 5:35 AM (in response to Anil Babu Samineni)
      
      The Standard and Target columns are expressions.
      
      This is my expression....
      Standard = SUM(DISTINCT [Yield.Standard])
      Target = sum(TOTAL [Yield.Standard])/sum(TOTAL Yield.Yield_Target)
      
      Yield_Target =
      SUM(DISTINCT [Yield.Standard])/
      (sum([Yield.Standard])/sum(Yield.Yield_Target))
      
      The result is below
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: Straight table calculated total row
        
        Anil Babu Samineni Jun 14, 2017 5:37 AM (in response to Tom Neal)
        
        Is there any dimensions in your report, Would you able to provide qvw for testing
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Straight table calculated total row
        
        Tom Neal Jun 14, 2017 6:11 AM (in response to Anil Babu Samineni)
        
        Yes here is a test qvw.
        
        Straight Table Test.qvw 155.2 K
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Straight table calculated total row
        
        Anil Babu Samineni Jun 14, 2017 7:24 AM (in response to Tom Neal)
        
        I am not using server now, May be stalwar1 cal help you
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Straight table calculated total row
        
        Sunny Talwar Jun 14, 2017 7:29 AM (in response to Anil Babu Samineni)
        
        kush141087 response should work here
        
        //Yield_Target
        SUM(DISTINCT [Target Content])/
        Sum(Aggr((SUM([Target Content])/SUM(Yield_Target)), Resource, Date))
        
        Like Show 1 Like (1)
        
        Actions
      - Re: Straight table calculated total row
        
        Shiva Nagesh Jun 14, 2017 5:40 AM (in response to Tom Neal)
        
        Use the columns in the straight table. You can reference earlier columns in the table,
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Straight table calculated total row
        
        Tom Neal Jun 14, 2017 5:47 AM (in response to Shiva Nagesh)
        
        How can I reference expression columns in a straight table?
        
        Like Show 0 Likes (0)
        
        Actions
      - Re: Straight table calculated total row
        
        Peter Rieper Jun 14, 2017 6:25 AM (in response to Tom Neal)
        
        You need to tickmark Total Mode to "Expression Total"
        
        Peter
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Straight table calculated total row
        
        Tom Neal Jun 14, 2017 6:29 AM (in response to Peter Rieper)
        
        It didn't make a difference.
        
        Thanks,
        Tom
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: Straight table calculated total row
        
        kushal chawda Jun 14, 2017 7:07 AM (in response to Tom Neal)
        
        try this expression
        
        SUM(DISTINCT [Target Content])/
        (sum(aggr(sum([Target Content])/sum(Yield_Target),Resource,Date)))
        
        Like Show 3 Likes (3)
        
        Actions
        
        Re: Straight table calculated total row
        
        Tom Neal Jun 14, 2017 7:32 AM (in response to kushal chawda )
        
        Worked perfectly...Thanks!
        
        Like Show 0 Likes (0)
        
        Actions

Go to original post