I have a nested pick/match/If expression in my QS script (at bottom) which I am using to create a bespoke formatted field for the front end app in order to avoid using expression in front-end (performance concerns)
This expression works for all field input values, when created in the front end
But when generated from script, for a very small minority of data points, it outputs the wrong format
e.g. for %%MeasureType='Numeric' - definitely correct...
it returns a financial format (in 2 specific rows) and a #,##0.00K format (1 row) and a #,##0.00% format (1 row)
despite returning correct format for all other values (approx 565 rows)
Please could someone spot something I'm missing !
The field expression :
Pick(Match(%%Flag_CumulativeMeasure,0,1)
,Pick(Match(%%MeasureType,'Numeric','Financial','Percentage','RAG')+1
,'n/a'
,If(log10(fabs([Measure Value]+1-1))>6
,Num(([Measure Value]+1-1)/1000000,'#,##0.00M')
,If(log10(fabs([Measure Value]+1-1))>3
,Num(([Measure Value]+1-1)/1000,'#,##0.00K')
,If(Round([Measure Value]+1-1,0.01)-Round([Measure Value]+1-1,0.1)<>0
,Num([Measure Value]+1-1,'#,##0.00')
,If(Round([Measure Value]+1-1,0.1)-Round([Measure Value]+1-1,1)<>0
,Num([Measure Value]+1-1,'#,##0.0')
,Num([Measure Value]+1-1,'#,##0')
)
)
)
)
,Money([Measure Value])
,Num([Measure Value],'#,##0.00%')
,Pick(Match([Measure Value],1,2,3)+1,[Measure Value],'Red','Amber','Green')
)
,Pick(Match(%%MeasureType,'Numeric','Financial','Percentage','RAG')+1
,'n/a'
,If(log10(fabs([Measure Cumulative Value]))>6
,Num([Measure Cumulative Value]/1000000,'#,##0.00M')
,If(log10(fabs([Measure Cumulative Value]))>3
,Num([Measure Cumulative Value]/1000,'#,##0.00K')
,If(Round([Measure Cumulative Value],0.01)-Round([Measure Cumulative Value],0.1)<>0
,Num([Measure Cumulative Value],'#,##0.00')
,If(Round([Measure Cumulative Value],0.1)-Round([Measure Cumulative Value],1)<>0
,Num([Measure Cumulative Value],'#,##0.0')
,Num([Measure Cumulative Value],'#,##0')
)
)
)
)
,Money([Measure Cumulative Value])
,Num([Measure Cumulative Value],'#,##0.00%')
,Pick(Match([Measure Cumulative Value],1,2,3)+1,[Measure Cumulative Value],'Red','Amber','Green')
)
) AS [Formatted Measure Value]
Regards
