Forum Discussion
Adding index number based on category
Hello, folks!
How can I add an index number that runs based on category type stated in another column? See the example picture. This is what I want to achiecve. I'd like the index column to give a number-series to all categories seperatly. Essentialy I would then be able to sort on category and get a continous series running from 1 to "whatever-number".
Aditionally I would like the index series in each category to run according to date in another column.
| Date | Category | Index |
| 15.03.2018 | Child | 1 |
| 12.04.2018 | Adult | 2 |
| 13.04.2018 | Adult | 3 |
| 02.04.2018 | Adult | 1 |
| 25.06.2018 | Child | 2 |
| 22.05.2018 | Adult | 4 |
| 27.09.2018 | Child | 4 |
| 22.08.2018 | Child | 3 |
| 15.10.2018 | Child | 5 |
| 02.10.2018 | Adult | 5 |
Is there any way to do this? I found this this forum-post interesting, but I can't really get it to fit my goal exactly. However, maybe some of you understand how to use this info for my purpose.
Best:
- Per-J.H.
What if you add another column called FkDte with this formula
=Table1[Date]+RANDBETWEEN(1,1000) / 10000
and then add another columns with this formula
=CALCULATE(
COUNTROWS( Table1 ),
ALLEXCEPT( Table1, Table1[Category] ),
Table1[FkDte] < EARLIER( Table1[FkDte] )
) + 1by the way, do you only have 2 columns in the original table (Date, Category), or do you have other columns that could avoid the usage of the FkDte column?
11 Replies
Anonymous Please try this using "New Column"
Index = RANKX(FILTER(Test13Rank,Test13Rank[Category]=EARLIER(Test13Rank[Category])),Test13Rank[Date],,ASC,Dense)
Both of the solutions mentioned here gave close to what I'm looking for. Almost there. I really liked the simplicity of this, last example.
There is one major problem, though. Whenever there is similar dates, this code returns the same index-value. I must have unique values for each row, even if there are the same dates. Its ok that observations on the same date have 4, 5 and 6 as values, as long as they are before the index number for a later date.
My new column gave me four #11 - values, as there are four observations on the same day. These values have to be 11, 12, 13 and 14.
Is there a neat way of getting this done?Anonymous Ok, then just remove the DENSE from the Rank. So it will be...
Index = RANKX(FILTER(Test13Rank,Test13Rank[Category]=EARLIER(Test13Rank[Category])),Test13Rank[Date],,ASC)
Thanks for your example. Very simple but it definitely gave solution to my problem!
Anonymous
You need to convert your date column to real dates and do it like this:
=
CALCULATE (
COUNTROWS ( Table1 ),
ALLEXCEPT ( Table1, Table1[Category] ),
Table1[Date] < EARLIER ( Table1[Date] )
)
+ 1
