topic Re: Aggregation calculation in QlikView

Aggregation calculation

Chin_IT — Sat, 16 Nov 2024 01:18:57 GMT

I am having issues with in Qlkiview into what calculation I will need to do.

=max( aggr( count([DATE]), Stock))/ sum (Stock)

This will return the values I will need, for example 0.7, or 0.65, however, I need that calculation, but to work out how to return the results that will be less than 1.

Re: Aggregation calculation

agigliotti — Mon, 10 Feb 2020 15:47:31 GMT

could you please explain better what number are you looking for?

Re: Aggregation calculation

Chin_IT — Mon, 10 Feb 2020 15:50:21 GMT

It is not I am looking for a number. The calculation should count how many are less than 1.

=max( aggr( count([DATE]), Stock))/ sum (Stock)

This calculation will return different values such as 0.7, or even 10. But I want that same calculation, but with where it also calculates how many are less than 1.

Re: Aggregation calculation

Chin_IT — Mon, 10 Feb 2020 15:55:14 GMT

If you seen from the image file, there is a column where you can 0.7.

However, the column should count how many are less than 1. In that picture it should return 1 as there is only one result. If there were 10 results with 0.7, then it will return 10. If there were 5 with 1.2, then it should return 0.

Re: Aggregation calculation

sunny_talwar — Mon, 10 Feb 2020 18:25:20 GMT

May be this

Sum(Aggr( If(Max(Aggr(Count([DATE]), Stock))/Sum(Stock) < 1, 1, 0) , Piece))

Re: Aggregation calculation

Chin_IT — Tue, 11 Feb 2020 08:43:19 GMT

Sunny,

Thank you so much! I believe it is working now.

Thanks,

Chin_IT

Re: Aggregation calculation

Chin_IT — Wed, 12 Feb 2020 12:08:33 GMT

Sunny,

How will it be if I were to use the calculation below?

Count([DATE])/Sum(Stock)

Re: Aggregation calculation

sunny_talwar — Wed, 12 Feb 2020 12:48:52 GMT

Not sure I follow your question?

Re: Aggregation calculation

Chin_IT — Wed, 12 Feb 2020 12:52:41 GMT

It is what you did for me last time, but for a different calculation

count([DATE])/Sum(Stock)

I want to find the values that are less than 1, and it will return a 0 if false, and a 1 if true

Re: Aggregation calculation

sunny_talwar — Wed, 12 Feb 2020 13:56:39 GMT

May be this

Sum(Aggr( If(Count([DATE])/Sum(Stock) < 1, 1, 0) , Piece))

Re: Aggregation calculation

Chin_IT — Wed, 12 Feb 2020 14:02:22 GMT

Sunny,

Thank you very much again. And if I wanted to calculate the average of the results, what would the calculation be?

Avg(Aggr( If(Count([DATE])/Sum(Stock) < 1, 1, 0) , Piece))

Would it be that calculation above?

Re: Aggregation calculation

sunny_talwar — Wed, 12 Feb 2020 14:22:30 GMT

Avg of what result?

Re: Aggregation calculation

Chin_IT — Wed, 12 Feb 2020 14:38:46 GMT

Sorry it is for another part.

It will be the average of the stockturn in the image file. But I will want to group it by the Piece Dimension. I have had a few tries, but, it keeps returning with null values

Re: Aggregation calculation

Chin_IT — Wed, 12 Feb 2020 14:40:36 GMT

Also, how would I can use the calculation to only show the values that are less than 1, and return true, so all the false values, will not appear in the table.

Sorry for asking so many, questions as I am fairly new to QlikView, but I really appreciate your help Sunny!

Re: Aggregation calculation

Chin_IT — Wed, 12 Feb 2020 15:47:33 GMT

Sunny,

Would you know why this happens, where some are not returning as true?

Re: Aggregation calculation

sunny_talwar — Wed, 12 Feb 2020 16:04:39 GMT

I really wish to help, but looking at just images is not helping me understand your issue... would you be able to provide a sample (qvw file) where you can show what you have and what exactly are you trying to get?

Re: Aggregation calculation

Chin_IT — Wed, 12 Feb 2020 16:12:09 GMT

I am not so sure I can, since this is a dashboard for a client. I will import some clearer images and representation of what I am looking for.

Stockturn less than 1

You provided me with the calculation, but the problem is that all the numbers that are under 1, do not return as 1, some do. (Can be seen in images Stockturn.PNG and Stockturn6.PNG)

Re: Aggregation calculation

Chin_IT — Wed, 12 Feb 2020 16:19:57 GMT

I have managed to fix this, I believe it is ok.

Re: Aggregation calculation

Chin_IT — Wed, 12 Feb 2020 16:21:34 GMT

My proposition is where as you can from the image file, how would I just be allowed to show just all the values with 1, not with 0. So all the true statements are in the table.

*Ignore the blue cirlce*