COUNTIF not working in TM1 Web ActiveSheets

[LoganNSE]

Hi,

We have below calculation to calculate number of cells in BX column with specific text (starting with 'Item' or 'Please') and is working fine in Excel however when we view the count in TM1 web - it always displays '0' (TM1 9.5.2 / Excel: 2003)

= SUM(COUNTIF($BX:$BX, "Item*"), COUNTIF($BX:$BX, "Please*"))

This works fine and I can see total count in Excel but not in TM1 Web.

Do i have to turn-on anything to make COUNTIF work in TM1 Web? or is there anything should be done?

Thanks & Regards,
Logan

[qml]

COUNTIF is on the list of excel functions supported by TM1 Web 9.5.2, so if you can't get it to work I'd be inclined to say it's a bug. But of course before you come to that conclusion, be sure you have restarted all the services and look into Excel upgrade/downgrade on the server as well. Is your Excel up to date with all the service packs and patches?

[Steve Rowe]

Or maybe its the more complex formula you have thats not supported?
Try a simple countif and if that works the you'll know. Then maybe break the formula up into multiple columns?

Mr HTH

[TomBr]

Hi,

I have come across issues with COUNTIF on TM1web before which I needed to work around.

Try the following :

Add the following formula into column BY
=IF(OR(LEFT(BX1,6)="Please",LEFT(BX1,4)="Item"),1,0)

Then your formula becomes
=SUM(BY:BY)

Tom

[LoganNSE]

Hi,

Tried everything however none of them worked-out in excel.

Finally, added a new column in the cube with numeric value '1' if there is an 'Item'/'Please', included this new column to active form, summed-up to get the count and the column was hidden.

This is the workaround to get that done

Thanks & Regards,
Logan