How may I use the Excel function Countifs under Spotfire Analyst 7.7.1

[Christophe Dubouch]

I would like to use an Excel function under Spotfire but could not achieve the same as in Excel

I have columns in Excel for which I would calculate a value if the condition is true.

If the condition is true, I would use the countifs function to count the number of times the criterias are true and then divide 1 by that number.

=IF(M2="YES";1/COUNTIFS(D:D;D2;G:G;G2;H:H;H2;M:M;M2);0)

 

Does some one may help me to translate the above example in Spotfire

 

Thank you in advance

[Jonathan Dowds]

Can you post an example dataset

[Christophe Dubouch]

I have attached a sample of the excel

In first tab "OTQ PER MONTH" the graph i would like to reproduce under SpotFire the Pivot table is also in the same table. The source is in the tabg "DATA" but in spot fire I would like to reproduce some calculated column like in Excel to avoid to have to inport juste the value of the column.

to small the size of the excel file, I have withdraw all the excel function exept for the first row.

I have mainly a problem to reproduce the calculate function like in Excel under SpotFire

How can I write the Expression of the cell L2 "QUANTITY OF DRAWING WITH MISTAKES" in my Excel file into a calculate column under Spotfire : =IF(I2="YES";1/COUNTIFS(B:B;B2;D:D;D2;E:E;E2;I:I;I2);0)

I am not sure I habe been clear enought :-)

Thank you for your Help

Kind regards.

[Khushboo Rabadia]

You can use regex replace function for achieving this:

case when [sHEETOTQ]="NO" then 1 / Len(RXReplace(RXReplace(Concatenate(Concatenate([bG],";",[DRAWING],";",[iSSUE],";",[sHEETID],";")),Concatenate([bG],";",[DRAWING],";",[iSSUE],";",[sHEETID],";"),"$","g"),"[^$]","","g")) else 0 endHere you can combine the conditions you like to validate using concatenate function. "Concatenate([bG],";",[DRAWING],";",[iSSUE],";",[sHEETID],";")" this part can be split up in a calculated column and then referenced in above custom expression if required.

If concatenated condition values exist in total concatenated list, thevalue replace it with $ for all occurences. Then again replace all the values except $ to nothing and take length of string containing $