Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))))))
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.