Simplifying nested 'ifs' and 'ands'

[N30fyte]

I have a sales team targeted on monthly Revenue. If they make at least a set target (vMinRev) every month, and they are in the top five revenue-earners, they get a bonus based on their ranking.

I want to display their bonus against their individual Revenue in a straight table, but only if they (a) hit the target and (b) are in the top five. If they don't meet both these conditions, then a big fat 0 is displayed against their revenue.

The following expression works exactly as I want:

if (Revenue >= vMinRev and rank(Revenue)=1,'$1000'
,if (Revenue >= vMinRev and rank(Revenue)=2,'$800'
,if (Revenue >= vMinRev and rank(Revenue)=3,'$600'
,if (Revenue >= vMinRev and rank(Revenue)=4,'$400'
,if (Revenue >= vMinRev and rank(Revenue)=5,'$200'
,'$0'
)))))

But I just wondered if there was a more elegant, less repetitive, way of expressing it.

Thanks!

[adilio_silva]

Hello!

I imagine that this approach works.

{<Revenue={">=vMinRev"}>}

Coalesce(
	Pick(
		Rank(Revenue),
		'$1000',
		'$800',
		'$600',
		'$400',
		'$200'
		),
		'$0'
	)

[N30fyte]

Hi @adilio_silva and thanks!

Unfortunately this displays a $200 bonus for a team member who ranked 5 for revenue but didn't hit the target: their bonus should be $0.

[Daniel_Castella]

Hi @N30fyte 

Try this formula:

RangeSum(Pick(Rank(Total Sum({<Revenue ={">=$(vMinRevLimit)"}>} Revenue)), '$1000', '$800', '$600', '$400', '$200'), '$0')

Basically, I set the condition of the revenue inside the ranking calculation to exclude the ones not fulfilling it to not be considered in the ranking. Then, with the Pick function I'm indicating which value is related to which position in the ranking. And finally, the RangeSum to assign the 0 to all the other rows.

Let me know if it works for you.

Kind Regards

Daniel

[N30fyte]

Thanks @Daniel_Castella ! In my app Revenue is a calculated master measure based on adding two values. If I insert its full name (in square brackets) into your formula I get an expression error.

If I use a 'straight' measure field name your expression compiles, but the values displayed in the 'Bonus' column are all zeroes (not '$0').

[Daniel_Castella]

Hi @N30fyte 

I thought the revenue was a numeric value coming directly from the script as a field. Could you, please, share the formula for me to understand how it is created?

Thank you

Daniel

[N30fyte]

Hi @Daniel_Castella 

OK, I have two values defined in the load script, I'd rather not reveal the actual names so let's call them revenue_x and revenue_y.

These give me two master measures: sum(revenue_x), let's call that RevX, and sum(revenue_y), let's call that RevY

The master measure I'm using in my table is

RevX+RevY, let's call that Total Revenue.

If I insert that master measure name into your expression as [Total Revenue], the expression yields an error.

If I insert just RevX into your expression, the expression yields an error.

In both the above cases, the expression colour-coding for the master measure names is not blue/purple as I would expect, but green.

If I insert revenue_x into your expression, the expression compiles correctly and the colour-coding for the field names is brown, as I would expect.

But in all three cases, the returned values in the 'Bonus' column in the chart for each team member is 0, not '$0'.

I'm about to log off for Christmas and I won't be back until the New Year. Many thanks for your input, I'll try to put together a 'dummy' load script that displays the issues and that I can share with you.

As I say, I've got a working solution, just wondered if it could be made more simple!

In the meantime have a great Christmas, and thanks once again.

N

[Daniel_Castella]

Hi @N30fyte 

Then, it is not possible to do with a set analysis formula. Set analysis only takes the data at datamodel level. Hence, it cannot consider master calculations or master dimensions.

If you want to simplify it and use set analysis, you should build the fields RevX, RevY and Total Revenue in the script.

In relation with the $0, my bad. Instead of RangeSum, use RangeMaxString. The effect is the same, but for strings instead of numbers. This will preserve the $ symbol. Other option is to keep the RangeSum and adjust the format with Number Formatting in the measure options.

Kind Regards

Daniel