How many months a product has been selling for

[rhyseretto]

Imagine the dataset;

Product	Month	Sales
Apple	Jan	$5
Apple	Feb	$3
Apple	Mar	$4
Apple	Mar	$2
Banana	Mar	$3
Banana	Apr	$3
Banana	May	$2
Coconut	Feb	$10
Coconut	Mar	$3

What I'd like to produce in Sense is a table which looks like this;

Product	Month	Sales	How many months sales
Apple	Jan	$5	1
Apple	Feb	$3	2
Apple	Mar	$4	3
Apple	Mar	$2	3
Banana	Mar	$3	1
Banana	Apr	$3	2
Banana	May	$2	3
Coconut	Feb	$10	1
Coconut	Mar	$3	2

Unsure where to start with this.

Thanks for any advice.

[stevejoyce]

In the front-end, you may be able to just get away with RowNo(), which does a count on your last dimension and resets back to 1 on your 2nd to last.

If it's row level in your data model, you can use peek() function, make sure data is sorted correctly like...

tbl:

load *

,if(peek(Product) <> Product and peek(Month) <> Month, 1,  if(peek(Product) = Product and peek(Month) = Month, peek([How many months sales]), if(peek(Product) = Product and peek(Month) <> Month, peek( [How many months sales] ) + 1)))

as [How many months sales]

resident <tbl>

order by Product asc, DateField asc