Jump to content

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

Christophe Dubouch

Recommended Posts

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.



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


Thank you in advance

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 1 year later...

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 $

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...