Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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;
@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:
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;
@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.
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;