2 Replies Latest reply on Nov 28, 2012 11:35 AM by Gowtham P

    Global Total for Sum over Min measure fields

    Gowtham P

      Hi,

       

      I have two fields TaskNo and Value. There are multiple entries for each task.

       

      The report displays Task Number and MIN (Value).

       

      I also would like to display Sum ( MIN( VALUE ) ) as Grand total. I tried using WINDOW_SUM(MIN([value])). But seems not working.

       

      Am I missing anything here? Any help is highly appreciated!!

       

      Thank you in advance.

       

      Best Regards

      Gowtham

        • 1. Re: Global Total for Sum over Min measure fields
          Tracy Rodgers

          Hi Gowtham,

           

          To get the total sum of min values in your grand total is a bit of a workaround (as maybe you've noticed, the grand total will use whatever the aggregation is on the measure, regardless of the table calculation that may be applied to it).

           

          First, create a calculated field similar to the following:

           

          IF FIRST()==0 THEN

          WINDOW_SUM(min([Value]),0,IIF(FIRST()==0,LAST(),0))

          END

           

          Then, right click on TaskNo in your data box and select Duplicate. Place the TaskNo (copy) field on the Level of Detail shelf. Place the above calculation on the view. Right click on it and select Compute Using-->TaskNo (copy).

           

          Hope this helps!

           

          -Tracy

          • 2. Re: Global Total for Sum over Min measure fields
            Gowtham P

            Awesome!! Thank you so much!!

             

            Now I see the power of "Level Of Detail"

             

            Thank you once again!