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

# Aggregation Query

Hi All,

Have been stuck into this for a while.

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.

• ###### Re: Aggregation Query

I get 5751.91 as result. Looks like you're using a different number of days for september.

• ###### Re: Aggregation Query

Or may be this:

=Sum(DISTINCT Aggr(Sum(TOTAL <Plant> Aggr(Avg(Days)*sum(Value), Plant, Month))/Sum(TOTAL <Plant> Aggr(If(Sum(Value) = 0, 0, Avg(Days)), Plant, Month)), Plant, Month))

Gives me:

The result is off because in your original attached Excel file you were multiplying by 31 for month of September. I changed it to 30 and the result seem to match.

Used Gysbert's QVW to make changes (Thanks to him for letting

• ###### Re: Aggregation Query

Does your September show 31 days?

• ###### Re: Aggregation Query

Thank you all for your responses.

Yeh sorry, have mistakenly considered 31 days for september.

Would check for your suggestion whether it works in my application.

Thank you once again, @Gysbert, @sunny, @swuehl.