topic Re: Aggregation Query in QlikView

Aggregation Query

yuvraj_33 — Sun, 31 Jan 2016 04:48:55 GMT

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.

Re: Aggregation Query

Gysbert_Wassenaar — Sun, 31 Jan 2016 15:43:45 GMT

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

Re: Aggregation Query

swuehl — Sun, 31 Jan 2016 15:43:58 GMT

Does your September show 31 days?

Re: Aggregation Query

sunny_talwar — Sun, 31 Jan 2016 15:59:40 GMT

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

yuvraj_33 — Sun, 31 Jan 2016 17:00:28 GMT

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.