topic Re: How to colour an expression by the desired dimension? in App Development

How to colour an expression by the desired dimension?

alespooletto — Mon, 18 Dec 2023 14:41:40 GMT

I have an expression that takes in a measure, and according to its value, gives me a colour for the columns if its value is above or below the average.

if(
Count({<ACTION_CODE={'COMPLETE'}, MainSFC={'Main SFC'}>} DISTINCT SFC) / Count(DISTINCT WorkingDay)
<
Avg(TOTAL Aggr(Count({<ACTION_CODE={'COMPLETE'}, MainSFC={'Main SFC'}>} DISTINCT SFC) / Count(DISTINCT WorkingDay), MainSFC)),
RGB(255, 0, 0), // Red color if below the average
RGB(0, 0, 255) // Blue color if above the average
)

The problem is, this is not giving me the correct results. Only in few cases does it work, but other cases when the value is below the mean, it still doesn't get coloured red.

Re: How to colour an expression by the desired dimension?

TcnCunha_M — Mon, 18 Dec 2023 14:35:32 GMT

could you print what happening because just look the expression I can't see nothing wrong

Re: How to colour an expression by the desired dimension?

alespooletto — Mon, 18 Dec 2023 14:42:02 GMT

Yes， thank you. I added the information

Re: How to colour an expression by the desired dimension?

Anil_Babu_Samineni — Mon, 18 Dec 2023 19:05:07 GMT

Always pause conditions and use table object to see.

if(
Count({<ACTION_CODE={'COMPLETE'}, MainSFC={'Main SFC'}>} DISTINCT SFC) / Count(DISTINCT WorkingDay)
<
Avg(TOTAL Aggr(Count({<ACTION_CODE={'COMPLETE'}, MainSFC={'Main SFC'}>} DISTINCT SFC) / Count(DISTINCT WorkingDay), MainSFC)),
1, // Red color if below the average
2 // Blue color if above the average
)

Re: How to colour an expression by the desired dimension?

TcnCunha_M — Tue, 19 Dec 2023 08:47:17 GMT

simple question, where are you adding this conditional to calculate ?

I understand you want to put red all the cases where they are going bellow the trend line,
this is trend line ?

Avg(TOTAL Aggr(Count({<ACTION_CODE={'COMPLETE'}, MainSFC={'Main SFC'}>} DISTINCT SFC) / Count(DISTINCT WorkingDay), MainSFC)

and could make both are master items ? to make live easy to make If (Master_ITEM_1 < mASTER_ITEM_2 , RED(),BLUE())

try like this

Re: How to colour an expression by the desired dimension?

alespooletto — Wed, 20 Dec 2023 07:23:05 GMT

So this is a good idea, but unfortunately it's really the second measure that doesn't seem to work out well. I have to work and improve it to make it functional.

Re: How to colour an expression by the desired dimension?

TcnCunha_M — Wed, 20 Dec 2023 08:00:52 GMT

OK

I see so you have a problem with your expression

I may suggest :

Count( TOTAL {<ACTION_CODE={'COMPLETE'}, MainSFC={'Main SFC'}>} DISTINCT SFC) / Count(WorkingDay)

I believe this should be your total scenario or something like this.

since there is a problem with your expression I believe the answer from Anil or my you should mark as right awnser

Re: How to colour an expression by the desired dimension?

alespooletto — Wed, 20 Dec 2023 08:53:58 GMT

Yes, thank you. I was able to solve the expression mistake, and it's thanks to both of you pointing out ways to find this out.

However @TcnCunha_M I still have an issue, as when I place the measures inside, I expect to be able to compare them all with the maximum average that I get. For example here:

I want to compare all of those rows against the 191 at the top. The current formula of the second column is :

Count( {< ACTION_CODE = {'COMPLETE'}, MainSFC = {'Main SFC'}>} distinct SFC) / Count(Distinct WorkingDay)

And instead of a breakdown by every row, I want it to always output its total. Can you help me figure this out? It seems like using Sum(All) and Sum(Total) it doesn't seem to work.

Re: How to colour an expression by the desired dimension?

TcnCunha_M — Thu, 21 Dec 2023 14:33:11 GMT

you probably need to aggregate by the dimensions and use total like

Sum ( TOTAL

Aggr(
Count( {< ACTION_CODE = {'COMPLETE'}, MainSFC = {'Main SFC'}>} distinct SFC) / Count(Distinct WorkingDay)
Fields )