4 Replies Latest reply: Jan 31, 2016 12:00 PM by Yuvraj Killedar RSS

    Aggregation Query

    Yuvraj Killedar

      Hi All,

      Have been stuck into this for a while.

      Need your valuable suggestions.

       

      Example.jpg

       

      As we can see in above image there are values for different plants over months in above table & below are the calculations performed for the same.

       

      My requirement is as below:

      Step 1: Multiply the cell values by no. of days for respective months

      Step 2: Further we sum up the derived values from step 1 at individual plant level & divide it by tot. no. of days for all months.

      (Note: Only to consider tot. days for months where values are not 0)

      Step 3: Finally we have to show the sum of values derived from step 2 for all plants as highlighted in green.

       

      I have been successful in achieving the Division column (in above image) so far for individual plant, i.e. when I select a particular plant.

      But when I do not select any plant, my aggregation expression fails since all plants do not have same tot. no. of days.

       

      I want to show the final value in a text box.

       

      The value that it should show is the one that is highlighted in green which is the sum at individual plant level.

       

      But somehow, I am getting a value which is 1058600(total value from SUM column) / 183 (Tot. no of days for all months) = 5784.7

       

      Any suggestions would be helpful for above mentioned scenario.

       

      Also attaching excel so that you can check for required calculations.