Trend-based colour graphics

[Jérôme5625]

Hello, I'm asking you for help because

I've been trying for several hours to find a solution to one of my requests.

I would like my bars on a bar graph to be different colours depending on an expression.

My graph represents the number of defects: (dimension = defaut and measure: count(defaut). This graph is dynamic and I have created a filter (field: ANNEE.SEMAINE on the sheet. 

I can see my graph evolving according to the weeks selected. All is fine

In order to be more precise, I would like the bars of my graph to have a different colour depending on the average number of faults registered on the fault concerned per week where the fault has already been declared. Then I would like the colour to be red if it is above the average, orange between 50 and 70¨% and green if it is below 50%.

Can you help me write this expression?

I tried in the first time this expression but not working:

""if(Sum(defaut) > Avg(Aggr(Sum(defaut), TOTAL ANNEE.SEMAINE)), rgb(255,0,0), rgb(0,0,255))"""

Thank you for advance

[HirisH_V7]

If you have already derived the average value then put that expression in below format at color conditional.

if(  'Avg Expression' >=40    ,Red(),

if('Avg Expression'>20 ,Green(),

if('Avg Expression' >20 and 'Avg Expression' <40 ,Yellow())) )

[Jérôme5625]

Hello,
After some research, I think that the problem is with my expression because the result of my average does not give me a number.

Can you help me with the syntax of the expression?

Avg(Aggr(count(defaut), TOTAL ANNEE.SEMAINE)

The idea is to have the average of the quantity of the selected defect per week compared with all the weeks in which the defect has been declared at least once.

Thanks for advance

[HirisH_V7]

Aggr(Avg(count(defaut)), ANNEE.SEMAINE) 

[Jérôme5625]

Hello HirisH, 

thanks for your feedback but the result is always the same. 

see the pictures. i don't understand why. 

thanks for advance. 

[HirisH_V7]

Could you come up with sample data and out put / Color code your looking at.

[Jérôme5625]

Hello Again,

i solved the issue because the syntax was wrong (wrong position of the ")").

the result works in part because as soon as I want to have the calculation for the entire database, I add the TOTAL indication (Avg(Aggr(count(defaut),TOTAL [ANNEE.SEMAINE])).

however, I no longer get a result.
What would be the syntax so that my average is taken into account on the entirety of my data without taking into account the active selection on this field?

i tested that not well : Avg(Aggr(count({<ANNEE.SEMAINE={*}>}defaut),[ANNEE.SEMAINE]))

thanks

[HirisH_V7]

Avg(Aggr(count({1<ANNEE.SEMAINE={*}>}defaut),[ANNEE.SEMAINE]))

[Jérôme5625]

Hello, I'm coming back to you on the subject.

I've finally found a formula which, depending on the number of defects for the week,

gives a view of the trend in the number of defects for the week selected. I've just put in the colour expression for my graph and my expression works when I select a bar on the graph (the colour changes to yellow) but the colour of this same bar when I don't select any bar is black. I think I'm not far off but I still need your help.

My expression: 

if(
Aggr(Count(defaut), defaut) > Aggr(Count({1<defaut={"$(=Only(defaut))"}>} defaut) / Count({1<defaut={"$(=Only(defaut))"}>} DISTINCT ANNEE.SEMAINE), defaut),
rgb(255, 0, 0), // Rouge
if(
Aggr(Count(defaut), defaut) > Aggr(0.7 * (Count({1<defaut={"$(=Only(defaut))"}>} defaut) / Count({1<defaut={"$(=Only(defaut))"}>} DISTINCT ANNEE.SEMAINE)), defaut),
rgb(255, 255, 0), // Jaune
rgb(0, 0, 0) // Noir
)
)

Have a nice day

[Jérôme5625]

Hello, 

Have you got any idea to help me?

Thanks for advance