Issue with set expression

[#1]

Hi gwassenaar‌,

I couldn't attach the sample to the reply. So created a new thread.

The following expression returns 4,777M

=RangeMax(0,(sum({<Year=,DATE = {'$(=vTimeFrame)'}>}AMT)+

sum({<Year=,DATE = {'$(=vTimeFrame)'}>}AMT2)))/1000000

I want to convert my result to 3 sig fig's.

So I defined a variable as :

vLogFn = pick(floor(log10(fabs($1))/3)+1, num($1, '#,##0.'), num($1/1000, '###K'),num($1/1000000, '###M'),num($1/1000000000, '###B'))

And then rewrote my original expression as:

$(vLogFn($(=RangeMax(0,(sum({<Year=,DATE = {'$(=vTimeFrame)'}>}AMT)+

sum({<Year=,DATE = {'$(=vTimeFrame)'}>}AMT2))))))

But the result I got is pretty erroneous.

Ideally, I was expecting something like 4.77B

Thanks in advance.

Regards,

Roy

[#2]

You don't need '=' while before RangeMax while using in the user defined function.

Try like below:

$(vLogFn($(RangeMax(0,(sum({<Year=,DATE = {'$(=vTimeFrame)'}>}AMT)+

sum({<Year=,DATE = {'$(=vTimeFrame)'}>}AMT2))))))

[Gysbert_Wassenaar]

I can't reproduce it with the document you posted either. If I put the expression

=RangeMax(0,(sum({<Year=,TRANSACTION_DATE = {'$(=vTimeFrame)'}>}NTPV_AMT)+

sum({<Year=,TRANSACTION_DATE = {'$(=vTimeFrame)'}>}NATIVE_TPV))) in a textbox it returns 3835776.55. If I put that same expression in a variable vExp and create another textbox using your vLogFunction in an expression =$(vLogFunction($(vExp))) then the output is 3.84M. If I use the expression =$(vLogFunction($(vExp)*1234)) it returns 4.73B. So, your vLogFunction works fine afaict.