Alternative for If condition in set expression

[jyoti19]

I want to replace 'if condition' in a set expression with an alternative expression giving the same result. But my data markers are getting vanished after that. How to resolve it? Below is the set expression for which i need an alternate expression without if condition but with the same results.

=
If(
Sum({<location={ 'ALL'}, metric={'free'},frequency_value-={'FY’},
type={'real '}, source_file_name={'$(v_filename) '}
>}sales)
>0,
Sum({<location={ 'ALL'}, metric={'free'},frequency_value-={'FY’},
type={'real '}, source_file_name={'$(v_filename) '}
>}sales)

[marcus_sommer]

If there are really sales which could zero or negative and/or which which couldn't be excluded with an extra set analyse condition like: ... sales = {">0"} ... you will further need such a conditional statement.

[jyoti19]

Thanks for the response Marcus.
Can you help me with the alternate expression ?
I used an alternate expression but data markers are getting vanished.
Thanks,
Jyoti

[henrikalmen]

I agree with the previous answer, but you could shorten the expression if you want, by moving the set expression outside the sum()-calculations. (Not that it changes the outcome, and it probably doesn't do anything to performance either.)

={<location={ 'ALL'}, metric={'free'},frequency_value-={'FY’},type={'real '}, source_file_name={'$(v_filename) '}>} If(Sum(sales)>0, Sum(sales))

[marcus_sommer]

Please elaborate in more details what's the aim of changing a working approach (performance, length, complexity, ...) and how looked your alternate expression and in which context is it used.

[jyoti19]

I have used the below alternate expression :

Sum({<location={ 'ALL'}, metric={'free'},frequency_value-={'FY’},
type={'real '}, source_file_name={'$(v_filename) '}>}

    sales * Only({<location={ 'ALL'}, metric={'free'},frequency_value-={'FY’},
type={'real '}, source_file_name={'$(v_filename) '}
>}

        {<sales= {"=Sum({<location={ 'ALL'}, metric={'free'},frequency_value-={'FY’},
type={'real '}, source_file_name={'$(v_filename) '}
>}sales) > 0"}>}

        sales

    )

)

The reason why I am not willing to use if condition is because it is affecting the app performance.

[Lech_Miszkiewicz]

@henrikalmen - On large data volumes external set analysis seem to decrease performance in our current testing so I would not use that syntax in this particular case.

Cheers

[marcus_sommer]

As far as you really need to check the sum() > 0 you will need a second aggregation within a conditional statement and there won't be much potential to improve the performance from the expression point of view. A simple check may be just to apply the single sum() - does it return the expected results?

More related to the performance will be the data-model (ideally it would be a star-scheme) and the object-dimensions (it should be native fields and not calculated dimensions).

[Lech_Miszkiewicz]

What is the actual purpose of of the If() statement because the result can really depend on the granularity of the object you are going to reflect such measure in. 

• If you put it in KPI object this will evaluate against total
• If you put it against dimension it will evaluate against such dimension and may give you different result when dimension is say Country or Region anbd different results when dimension is InvoiceId as that could simply remove returns for example.

On the other hand if your goal is to avoid showing negative values than maybe you can just use custom number format like '#,##0.0;;0'