Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
yuvraj_33
Partner - Contributor III
Partner - Contributor III

Aggregation Query

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.

4 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
swuehl
MVP
MVP

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:

Capture.PNG

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.

Capture.PNG

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

yuvraj_33
Partner - Contributor III
Partner - Contributor III
Author

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.