topic Re: Average not working in App Development

Average not working

Chris_Buck — Tue, 23 Jul 2024 13:27:08 GMT

Hello,

I am still getting to grips with Qliksense, help is very much appreciated.

I want to average a set of data and have it so it shows above the data like in the image below:

I tried the following formula in the measure section, but no luck

Avg((

(24*Count({$}distinct Date)

-Sum({$<[Excused / Un-Excused]={$(vDTtype)}>}[Downtime Hours]) // "Un-Excused Downtime","N/A"

+Sum({1-$<[Downtime Hours]={"*"}>}0))

(24*Count({$}distinct Date))

))+ 0.00001

Thank you for taking the time to look at this one

Re: Average not working

Or — Tue, 23 Jul 2024 13:43:29 GMT

That will not work - as the formula window will tell you, nested aggregation is not allowed.

If you want to nest aggregation, you'll need to use the Aggr() function. I'd suggest reading up on that as it may provide what you need in this case. You'd need something like:

Avg(Aggr(YourFormula,Month,[Plant Name]))

Re: Average not working

Chris_Buck — Tue, 23 Jul 2024 16:49:44 GMT

Is this a nested aggregation? I thought the formula would be simple.

Re: Average not working

Or — Tue, 23 Jul 2024 18:17:44 GMT

Yes, it is - you have a bunch of Sum() and Count() functions contained within an Avg() function, so, nested aggregation.

Re: Average not working

Chris_Buck — Wed, 24 Jul 2024 07:53:42 GMT

well, this was the original formula from the sheet that I duplicated from and I wanted the average so I added the avg function in front of it. Should I create a new measure to get the average then?

(

(24*Count({$}distinct Date)

-Sum({$<[Excused / Un-Excused]={$(vDTtype)}>}[Downtime Hours]) // "Un-Excused Downtime","N/A"

+Sum({1-$<[Downtime Hours]={"*"}>}0))

(24*Count({$}distinct Date))

))+ 0.00001

Re: Average not working

Or — Wed, 24 Jul 2024 08:13:34 GMT

As I said, if you want to take an existing aggregation and get an aggregation of that, you would need to use aggr().

Since I don't know what exactly this expression is meant to do, and what the data model is, I have no idea if there's an easier alternative.

Note that totals in general will use the same formula as the underlying measure, so if you add a total, that formula will be applied. It sounds like you might be able to get away with just using this formula and dividing by the number of Plants.

Re: Average not working

Chris_Buck — Thu, 25 Jul 2024 08:33:06 GMT

I don't know what the expression is meant to do either. I am extracting the data on a monthly basis and I need to average it then enter the value somewhere else, currently I am doing it manually, but I would have liked to have the information in one of the rows under the month column, so that when I filter the plants, the information is there. If there is no other way, then I'll just continue doing it manually 😅

Re: Average not working

Or — Thu, 25 Jul 2024 09:44:39 GMT

It surely is possible, but it does require you understand the formula you're working with, unfortunately, at least to a basic degree. Sounds like manual may be the way to go here.

Re: Average not working

Chris_Buck — Thu, 25 Jul 2024 14:06:19 GMT

I see, thank you anyway 😊