Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

yuvraj_33
New Contributor II

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

Re: Aggregation Query

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


talk is cheap, supply exceeds demand
MVP
MVP

Re: Aggregation Query

Does your September show 31 days?

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:

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
New Contributor II

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.

Community Browser