Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lobmeister165
Contributor III
Contributor III

Bespoke Formatting Expression Error

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
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

2 Replies
lobmeister165
Contributor III
Contributor III
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP