Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for
Did you mean:
Contributor III

## Average not working

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
Labels (1)
• ### General Question

1 Solution

Accepted Solutions
MVP

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.

8 Replies
MVP

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]))

Contributor III
Author

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

MVP

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

Contributor III
Author

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

MVP

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.

Contributor III
Author

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 😅

MVP

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.

Contributor III
Author

I see, thank you anyway 😊

Tags