Compare values in the same column

[frerechapeau]

Hi all,

Here's the data I'm working with :

Product	Production Start Date
Product_1	01/01/1993
Product_2	01/01/1999
Product_3	01/01/1995
Product_4	01/01/2002
Product_5	01/01/2001

I want to add a calculated field that would contain for each product, the total number of older products. In this case, it would look like this :

Product	Production Start Date	Number of older products
Product_1	01/01/1993	0
Product_2	01/01/1999	2
Product_3	01/01/1995	1
Product_4	01/01/2002	4
Product_5	01/01/2001	3

Does someone know how to achieve this ?

[Taoufiq_Zarra]

@frerechapeau  maye be is just a rank by Date

like :

in dimension Product

and in measure use this expression :

rank(total -[Production Start Date])-1

output:

[stevejoyce]

Can multiple products have same production start date?  Can1 product have multiple production start date?

if both are NO, then it's a simple rowno() with sorting the table.  If either are possible, then you can use the peek() function to increment.  If either are possible let me know, i can give an example with peek function to handle that scenario.

temp_data:
load * inline [
Product, Production Start Date
Product_1, 01/01/1993
Product_2, 01/01/1999
Product_3, 01/01/1995
Product_4, 01/01/2002
Product_5, 01/01/2001
]
;

data:
load
*
,rowno()-1 as [Number of older products2]

resident temp_data
order by [Production Start Date] asc;

drop table temp_data;

[frerechapeau]

@stevejoyce Thank you for your help!

Two products can indeed have the same Production Start Date, but a product cannot have multiple production start dates.

[stevejoyce · Accepted solution]

if you want on-the-fly calculation via fron-end @Taoufiq_Zarra has what you are looking for.  If it can be in the load script then this should work:

temp_data:
load * inline [
Product, Production Start Date
Product_1, 01/01/1993
Product_2, 01/01/1999
Product_3, 01/01/1999
Product_4, 01/01/2002
Product_5, 01/01/2001
]
;

data:
load
*
,rowno()-1 as [Number of older products BREAKTIES]
,if(rowno()=1, 0, if(peek([Production Start Date]) <> [Production Start Date], peek([Number of older products BREAKTIES]) + 1, peek([Number of older products]))) as [Number of older products]
resident temp_data
order by [Production Start Date] asc;

drop field [Number of older products BREAKTIES];
drop table temp_data;