Filter by IF and Like?


https://www.nevron.com/Forum/Topic9121.aspx
Print Topic | Close Window

By Jonathan Green - 5 Years Ago
If I use the following  =IF(LIKE(Fields!Symbol, "1tBRN"), Fields!closeval, 40 ) as a data value against the following data I get a nice chart:



date
Symbolcloseval
TypeTextTextDouble
04-12-20151tBRN 57.86
04-13-20151tBRN 57.93
04-14-20151tBRN 58.43



If I use the dame formula against mixed data
dateSymbolcloseval
TypeTextTextDouble
04-12-20151tBRN 57.86
04-12-20151tWBS 51.52
04-13-20151tBRN 57.93
04-13-20151tWBS 51.91
04-14-20151tBRN 58.43
04-14-20151tWBS 53.29
04-15-20151tBRN 60.32






I get :
An error occured during the web part rendering. The error was: Pivot processing failed during: Phase: Processing Pivot - Data Matrix - Properties, Action: Evaluating - =IF(LIKE(Fields!Symbol, "1tWBS"), Fields!closeval, 45 ). Error was: Unable to cast object of type 'Nevron.FormulaEngine.NVariant[]' to type 'System.IConvertible'.

By Nevron Support - 5 Years Ago
The Fields!Symbol reference returns an array. The LIKE function convers that array to Boolean values. However the IF function requires that the passed value is convertible to Boolean. You need to refactor the expression as either:

=IF(FIRST(LIKE(Fields!Symbol, "1tBRN")), Fields!closeval, 40 )

or even better:

=IF(LIKE(FIRST(Fields!Symbol), "1tBRN"), Fields!closeval, 40 )

By Jonathan Green - 5 Years Ago
Super .So if I then wanted to add a second value to display data from the 1tWBS symbol I could then use then use:

=IF(LIKE(LAST(Fields!Symbol), "1tWBS"), Fields!closeval, 40 ) ?
By Nevron Support - 5 Years Ago
We are not familiar with your category data groupings -> you perhaps need to first group the data by Fields!Symbol and then make some statistics on it.