Bespoke Formatting Expression Error

[lobmeister165]

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

[lobmeister165]

..by the way the +1-1 was only added to try and fool QS but issue was still there before I added this

[rwunderlich · Accepted solution]

I think you are seeing this problem.

https://qlikviewcookbook.com/2020/02/num-script-vs-chart/

Solution is at the bottom of the post. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com