1 Reply Latest reply on Dec 20, 2018 11:55 AM by Patrick Van Der Hyde

    Grand total issue

    Lakshmi Palisetty

      Hi All ,

      I am facing some strange scenario while displaying GRAND TOTAL


      I have attached .twbx file.

      below is my requirement


      1) Displaying Avarage Price for Baseline and Current (order amount/Order Quantiy) -- Its working fine in rowlevel

      2) Here i have to calculate OPI $ vakue by using business logic i.e ( Current Avarage Price - Baseline Avarage Price) * Current Order Quantity -- This is working fine at row level


      In this attached .twbx file to explain the issue i am using 2 account names as an example.The issue here is for one of the account SETPOINT INTEGRATED SOLUTIONS (CN) the grand total should display 22278 for the other account grand total should display 7281


      For first account grand total is displaying as expected (22278) where other account not displaying anything for OPI $ Grand Total.So to resolve this NUCLEAR grand total issue  I used ZN function for OPI calculated field then GRAND TOTAL is coming as expected 7281 where as

      SETPOINT Grand total displaying different random value which is earlier displaying properly before using ZN


      In nut shell, the row level data is displaying properly but grand total is not coming as expected.


      Could you please do the needful



        • 1. Re: Grand total issue
          Patrick Van Der Hyde

          Hello Lakshmi,


          The field OPI Unit relies on a calculation of Current Avg Price - Baseline Avg price.   This field is null when either of the two fields are set to null.   so for instance - 0A15SY  - has a current avg price of 207 with a baseline avg price of null.  When using the non aggregate calculation - the value output is null.   The row for Prod number 7281 has no average price value so the field is coming back as null for Contro Valve.


          Once the values are all modified with ZN() so OPI Unit appears as - ZN([Current Avarage Price]) - zn([Baseline Avarage Price])  then the values expressed are a result of filling in all of those blanks with zero.   This fixes the Contro Valve numbers apparently but I'm not sure why they would be incorrect for Setpoint with this fied.  I show the OPI $ value in this case would be 1,871,765


          I hope that helps explain whats going on so you can determine what the correct calculations should be.