Aggregation Query

[yuvraj_33]

Hi All,

Have been stuck into this for a while.

Need your valuable suggestions.

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.

[Gysbert_Wassenaar]

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

[swuehl]

Does your September show 31 days?

[sunny_talwar]

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

[yuvraj_33]

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.