topic Average trend line calculation in App Development

Average trend line calculation

Umeiko — Wed, 17 May 2023 03:03:44 GMT

I wondered if it is possible to change bar-color "above average trend line", like the picture I posted above?

I tried to write an expression to calculate the RGB color but it doesn't work.

It might be because that I don't know what the average trend line formula is.

The expression shows correct but doesn't work.

How can I solve the problem?

Re: Average trend line calculation

Chanty4u — Fri, 19 May 2023 17:19:13 GMT

Use this measure

Above(Sum(Sales), Avg(TOTAL Sum(Sales)))

In colours tab colours by expression

Re: Average trend line calculation

Kushal_Chawda — Sat, 20 May 2023 23:04:31 GMT

@Umeiko what is your bar chart expression?

Re: Average trend line calculation

Umeiko — Mon, 22 May 2023 00:04:31 GMT

@Kushal_Chawda @Chanty4u

My bar chart expression is "budget achievement rate" like below:

= 1+(Sum({$<[Profit_item]={'Booking'}> } [Curmo_billing]) -Sum({$<[Profit_item]={'Booking'}> } [curmo_budget]))
/ fAbs(Sum({$<[Profit_item]={'Booking'}> } [curmo_budget]))

That's why I don't know how to get the average trend line.

I tried expression below in color tab, qlik window shows "correct" but it doesn't work

=if( 1+(Sum({$<[Profit_item]={'Booking'}> } [Curmo_billing]) -Sum({$<[Profit_item]={'Booking'}> } [curmo_budget]))
/ fAbs(Sum({$<[Profit_item]={'Booking'}> } [curmo_budget]))> avg(Total aggr(avg(1+(Sum({$<[Profit_item]={'Booking'}> } [Curmo_billing]) -Sum({$<[Profit_item]={'Booking'}> } [curmo_budget]))
/ fAbs(Sum({$<[Profit_item]={'Booking'}> } [curmo_budget]))))), RGB(0,200,0), RGB(220,100,50))