Optimization of formula

Question & Answer
, ,
1

This formula can be optimized ?

ifelse(

{ACTION REQUIRED} = 1 AND {ITEM SCORE} >= 0.985 AND {BUSINESS SEGMENT} = "RLUS", "Significantly Exceeds",

{ACTION REQUIRED} = 1 AND {ITEM SCORE} >= 0.955 AND {BUSINESS SEGMENT} = "RLUS", "Exceeds",

{ACTION REQUIRED} = 1 AND {ITEM SCORE} >= 0.93 AND {BUSINESS SEGMENT} = "RLUS", "Meets",

{ACTION REQUIRED} = 1 AND {ITEM SCORE} >= 0.851 AND {BUSINESS SEGMENT} = "RLUS", "Mostly Meets",

{ACTION REQUIRED} = 1 AND {ITEM SCORE} >= 0 AND {BUSINESS SEGMENT} = "RLUS", "Does Not Meet",


{ACTION REQUIRED} = 1 AND {ITEM SCORE} >= 0.985 AND {BUSINESS SEGMENT} = "FNWL", "Significantly Exceeds",

{ACTION REQUIRED} = 1 AND {ITEM SCORE} >= 0.955 AND {BUSINESS SEGMENT} = "FNWL", "Exceeds",

{ACTION REQUIRED} = 1 AND {ITEM SCORE} >= 0.93 AND {BUSINESS SEGMENT} = "FNWL", "Meets",

{ACTION REQUIRED} = 1 AND {ITEM SCORE} >= 0.851 AND {BUSINESS SEGMENT} = "FNWL", "Mostly Meets",

{ACTION REQUIRED} = 1 AND {ITEM SCORE} >= 0 AND {BUSINESS SEGMENT} = "FNWL", "Does Not Meet",


{ACTION REQUIRED} = 1 AND {ITEM SCORE} >= 0.95 AND {BUSINESS SEGMENT} = "Genpact", "Meets",

{ACTION REQUIRED} = 1 AND {ITEM SCORE} >= 0 AND {BUSINESS SEGMENT} = "Genpact", "Does Not Meet",

""

)

2

Hello @Samiksha

Yes, your formula can be optimized by reducing redundancy. Here’s a more efficient version.

ifelse(
{ACTION REQUIRED} = 1 AND {BUSINESS SEGMENT} = “RLUS” AND {ITEM SCORE} >= 0.985, “Significantly Exceeds”,
{ACTION REQUIRED} = 1 AND {BUSINESS SEGMENT} = “RLUS” AND {ITEM SCORE} >= 0.955, “Exceeds”,
{ACTION REQUIRED} = 1 AND {BUSINESS SEGMENT} = “RLUS” AND {ITEM SCORE} >= 0.93, “Meets”,
{ACTION REQUIRED} = 1 AND {BUSINESS SEGMENT} = “RLUS” AND {ITEM SCORE} >= 0.851, “Mostly Meets”,
{ACTION REQUIRED} = 1 AND {BUSINESS SEGMENT} = “RLUS” AND {ITEM SCORE} >= 0, “Does Not Meet”,

{ACTION REQUIRED} = 1 AND {BUSINESS SEGMENT} = “FNWL” AND {ITEM SCORE} >= 0.985, “Significantly Exceeds”,
{ACTION REQUIRED} = 1 AND {BUSINESS SEGMENT} = “FNWL” AND {ITEM SCORE} >= 0.955, “Exceeds”,
{ACTION REQUIRED} = 1 AND {BUSINESS SEGMENT} = “FNWL” AND {ITEM SCORE} >= 0.93, “Meets”,
{ACTION REQUIRED} = 1 AND {BUSINESS SEGMENT} = “FNWL” AND {ITEM SCORE} >= 0.851, “Mostly Meets”,
{ACTION REQUIRED} = 1 AND {BUSINESS SEGMENT} = “FNWL” AND {ITEM SCORE} >= 0, “Does Not Meet”,

{ACTION REQUIRED} = 1 AND {BUSINESS SEGMENT} = “Genpact” AND {ITEM SCORE} >= 0.95, “Meets”,
{ACTION REQUIRED} = 1 AND {BUSINESS SEGMENT} = “Genpact” AND {ITEM SCORE} >= 0, “Does Not Meet”,

“”
)

1 Like
3

Hi @Samiksha , @Xclipse ,

There is room for further optimization.
Since the thresholds for ITEM SCORE are same for RLUS and FNWL segments, those checks can be combined by using nested OR condition. Likewise, the ‘Does not meet’ rule can be combined across all three segments.
See below.

ifelse(
{ACTION REQUIRED} = 1 AND {ITEM SCORE} >= 0.985 AND ({BUSINESS SEGMENT} = "RLUS" OR {BUSINESS SEGMENT} = "FNWL"), "Significantly Exceeds",
{ACTION REQUIRED} = 1 AND {ITEM SCORE} >= 0.955 AND ({BUSINESS SEGMENT} = "RLUS" OR {BUSINESS SEGMENT} = "FNWL"), "Exceeds",
{ACTION REQUIRED} = 1 AND {ITEM SCORE} >= 0.93 AND ({BUSINESS SEGMENT} = "RLUS" OR {BUSINESS SEGMENT} = "FNWL"), "Meets",
{ACTION REQUIRED} = 1 AND {ITEM SCORE} >= 0.851 AND ({BUSINESS SEGMENT} = "RLUS" OR {BUSINESS SEGMENT} = "FNWL"), "Mostly Meets",
{ACTION REQUIRED} = 1 AND {ITEM SCORE} >= 0.95 AND {BUSINESS SEGMENT} = "Genpact", "Meets",
{ACTION REQUIRED} = 1 AND {ITEM SCORE} >= 0 AND ({BUSINESS SEGMENT} = "RLUS" OR {BUSINESS SEGMENT} = "FNWL" OR {BUSINESS SEGMENT} = "Genpact"), "Does Not Meet",
""
)```

Marking this as solution here. Please let us know if you have further questions.

Regards,
Arun Santhosh
Pr QuickSight SA
1 Like
4

Thanks for this optimization , will try and let you know

But I also wanted to know that is there anyone from the community who is taking sessions online which I can attend or may be if you can suggest some refrences which will cover overall Quicksight as I am totally new for QS and facing so many challenges while working and Management is expecting lot to put reports into production and it is a bit challeneging for me I am trying but before I learn deadline they have set of a limited period in which I am not able to learn , research and implement things that too I am working on someone else’s report and that person left the project in between I am not getting any help.

Please help me in this . I want training for Quicksight from Basic to Advance complete. Please help me out .

5

Hi @Samiksha,

Sorry to hear that you are going through a stressful patch at work.

We do host online learning events for the community. Details of the upcoming sessions are available in the community events calendar - Amazon QuickSight Community

Our learning learning dashboard also lists out lots of learning material for helping users ramp up - QuickSight Learning Dashboard

Hope you find these helpful.

Regards,
Arun Santhosh
Pr QuickSight SA

2 Likes
6

How to enroll for these courses ?