Nested ifelse with parameter - syntax issue

Question & Answer
,
1

Hi,
i’m having an syntax issue with such below ifelse function.

{stdDev1XOutliersIndicator(by Project)} - is a calculated field generating TRUE/FALSE

I want to compare that output vs harcoded parameter value to assign respective labels. Thank in advance for any inputs.

ifelse

(

{stdDev1XOutliersIndicator(by Project)} = ${OutlierTRUE}, ‘Outlier 1std’,

{stdDev2XOutliersIndicator(by Project)} = ${OutlierTRUE}, ‘Outlier 2std’,

{stdDev3XOutliersIndicator(by Project)} = ${OutlierTRUE}, ‘Outlier 3std’,

{stdDev1XOutliersIndicator(by Project)} = ${OutlierFALSE}, ‘Regular 1std’,

{stdDev2XOutliersIndicator(by Project)} = ${OutlierFALSE}, ‘Regular 2std’,

{stdDev3XOutliersIndicator(by Project)} = ${OutlierFALSE}, ‘Regular 3std’,

NULL

)

2

Hello @Rad !

My thought is it could be two things: 1) The (by Project) having parenthesis could be throwing off the syntax and/or 2) you need to seperate each line with an AND/OR statement to tell the command to break between potential options.

It could also be a mixture of the two.

Let me know if this helps!

2 Likes
3

Hi @duncan thank you for prompt feedback! You were right, when removed parenthesis everything works as expected. However when validating the output i realized that the calculated field is returning only some values (on 1 st level). Let me walk you through my problem:

Those calculated fields are generating TRUE/FALSE values depending on some other fields:

stdDev1XOutliersIndicatorByProject
stdDev2XOutliersIndicatorByProject
stdDev3XOutliersIndicatorByProject

The work totally fine when using then as 3 separate controls within dashboard, so user can select TRUE/FALSE on each level:

image

However I would like to consolidate all the 3 control within 1 control, so i’m trying to include the hardcoded parameter values ${OutlierTRUE} ${OutlierFALSE into the calculated field. This is the calculated field for this excercise, i’ve recreated as per your advice:


ifelse

(

stdDev1XOutliersIndicatorByProject = ${OutlierTRUE}, 'Outlier 1 std dev',

stdDev2XOutliersIndicatorByProject = ${OutlierTRUE}, 'Outlier 2 std dev',

stdDev3XOutliersIndicatorByProject = ${OutlierTRUE}, 'Outlier 3 std dev',

stdDev1XOutliersIndicatorByProject = ${OutlierFALSE}, 'Regular 1 std dev',

stdDev2XOutliersIndicatorByProject = ${OutlierFALSE}, 'Regular 2 std dev',

stdDev3XOutliersIndicatorByProject = ${OutlierFALSE}, 'Regular 3 std dev',

NULL

)

The control works fine but apparently it’s returning values only on 1st levels i.e.

'Outlier 1 std dev'
'Regular 1 std dev'

image

For instance for this scenario, i was expecting to see 3 labels:

image
image938×252 14.3 KB

stdDev1XOutliersIndicatorByProject = TRUE / Outlier 1 std dev - WORKING FINE

stdDev1XOutliersIndicatorByProject = FALSE / Regular 1 std dev - WORKING FINE

stdDev2XOutliersIndicatorByProject = FALSE / Regular 2 std dev - MISSING LABEL

stdDev3XOutliersIndicatorByProject = FALSE / Regular 3 std dev - MISSING LABEL

For the validation I’m filtering only by filter, control is set as “select all”.

I guess it’s related to the fact that ifelse assignes only 1 label based on the 1st calculated field and ingores other 2. Otherwise the records would must be duplicated to include all the labels. Could you take a look and advice what could be improved here or if it’s even doable?

Many thanks in advance!

4

@duncan it’s solved now, you can disregard my follow up, cheers.

1 Like
5

@Rad No problem! Feel free to post your solution to add to the community as well.

6

@duncan I’ve created a parameter outlier with integer values (static default value = 1), so i can include this into calculated field within nested ifelse function outlierFilter:

ifelse(${outlier} = 1, avgOver(Minutes,[project],PRE_AGG) + stdevpOver(Minutes,[project],PRE_AGG),

ifelse(${outlier} = 2, avgOver(Minutes,[project],PRE_AGG) + (stdevpOver(Minutes,[project],PRE_AGG)*2),

ifelse(${outlier} = 3, avgOver(Minutes,[project],PRE_AGG) + (stdevpOver(Minutes,[project],PRE_AGG)*3),Minutes)))

Having this added i have a supported filter outlierFilterHelper within analysis, so I can filter by the outliers:

ifelse(Minutes>outlierFilter,“Yes”,“No”)

Last step is to create a control based on the parameter (with 0,1,2,3 values) and it’s working as expected. When outlierFilterHelper is set as “No” then when i choose “1” in control I have all the outliers excluded at first level of standard deviation.

1 Like