CODEWITHSUNDEEP
spotfire
Solomon
Solomon

Reputation: 11

Identifying Repeat values in a column - Spotfire

Is there a way in spotfire to identify repeating values in a column based on different dates?

Suppose there are columns: Date, Store number, Result

We see that store numbers 27, 35 are repeated in different dates and it should display in result column as Y. But if store numbers are repeated in a same date it should not be taken into account.

+------------+----------+-----------------------------+
|    Date    | Store No | Result in Calculated Column |
+------------+----------+-----------------------------+
| 05/14/2015 |       23 |                             |
| 05/14/2015 |       27 | Y                           |
| 05/14/2015 |       23 |                             |
| 05/14/2015 |       35 | Y                           |
| 05/14/2015 |        2 |                             |
| 05/14/2015 |       27 | Y                           |
| 05/15/2015 |       31 |                             |
| 05/15/2015 |      101 |                             |
| 05/15/2015 |       88 |                             |
| 05/15/2015 |       31 |                             |
| 05/15/2015 |       27 | Y                           |
| 05/15/2015 |       35 | Y                           |
+------------+----------+-----------------------------+

Upvotes: 1

Views: 2793

Answers (1)

S3S
S3S

Reputation: 25112

You need to use INTERSECT() with an OVER clause.

If(Count([Store No]) over (Intersect([Store No],All([Date])))<>Count([Store No]) over (Intersect([Store No],[Date])),"Y")

This is basically saying if the count of the store number for the date of the current row doesn't equal the count of the store number across all dates, then place a Y.

Upvotes: 2

Related Questions