Average not working

[Chris_Buck]

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 

[Or]

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

[Chris_Buck]

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

[Or]

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

[Chris_Buck]

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

[Or]

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.

[Chris_Buck]

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 😅

[Or · Accepted solution]

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.

[Chris_Buck]

I see, thank you anyway 😊