topic Re: Calculating average on a measure instead of a column in App Development

Calculating average on a measure instead of a column

saurabh_Qlik1 — Fri, 15 Nov 2024 21:47:16 GMT

I have a table with multiple columns. Using few of the columns I have to calculate another column named 'Final Data'. I am doing this by a measure instead of adding a calculated column in the table. Further, I need to create a measure that calculates AVERAGEIF equivalent on 'Final Data' by applying various conditions and then plot on a chart.

As it turns out, the following expression gives me an error

Avg(If([Asset] = 'A', [Final Data])) where 'Asset' is the name of an actual column in my master table and ‘Final Data’ is the measure I used to create the column I needed.

This seems to only work for actual columns and not measures.

Please suggest what can be done.

Re: Calculating average on a measure instead of a column

AlBoo8749 — Fri, 19 May 2023 16:47:47 GMT

Does If([Asset] = 'A', Avg([Final Data]),0) work for you?

Re: Calculating average on a measure instead of a column

saurabh_Qlik1 — Fri, 19 May 2023 16:53:31 GMT

No, it still returns an error.

Re: Calculating average on a measure instead of a column

Chanty4u — Fri, 19 May 2023 17:20:29 GMT

Try this

Avg(Aggr(Sum({<Asset={'A'}>} [Final Data]), Dimension1, Dimension2))

Re: Calculating average on a measure instead of a column

AlBoo8749 — Fri, 19 May 2023 17:32:58 GMT

What is an example of the entries for 'Dimension1' and 'Dimension2'? I am asking because I have a similar problem. Are they other fields?

Re: Calculating average on a measure instead of a column

Chanty4u — Fri, 19 May 2023 17:33:56 GMT

Yes your dimensions fields it will aggregate your data based on that fields

Re: Calculating average on a measure instead of a column

AlBoo8749 — Fri, 19 May 2023 17:38:02 GMT

Thank you!

Re: Calculating average on a measure instead of a column

Chanty4u — Fri, 19 May 2023 17:38:42 GMT

Is that solution working?

Re: Calculating average on a measure instead of a column

Kushal_Chawda — Fri, 19 May 2023 23:30:03 GMT

@saurabh_Qlik1 what is the expression of Final Data?

Re: Calculating average on a measure instead of a column

saurabh_Qlik1 — Mon, 22 May 2023 06:45:14 GMT

Hi, thanks for your response.

The expression for 'Final Data' is a simple one:

=[Spread] / (([Change 1] + [Change 2] + [Change 3] + [Change 4])/4

where all these are columns in my dataset.

Re: Calculating average on a measure instead of a column

saurabh_Qlik1 — Mon, 22 May 2023 07:22:57 GMT

It didn't seem to work for me.

As a workaround, instead of creating a measure for 'Final Data', I just added the formula to calculate 'Final Data' in the expression that goes in the chart. Example of my final expression

Avg(If([Asset] = 'A', ([a] + [b] + [c])))