Window function - unique ranking

NateBI · 2023-01-11T20:36:51+00:00

There was an error rendering this rich post.

NateBI

Hi all,

I'm trying to create the following logic for column '?' based on the header column:

context

header: the original column I want to rank against

temp: a constant 1 for the row number logic

row number: a row number ordered by temp ascending (recommended on the forum)

header identifier: a conditional column using 'CASE WHEN [columnA] = 'Market' THEN 'START' END'

Currently trying a rank & window using rank-ordered by the 'row number' and partitioned by the header identifier but at the point of 'START' it removes that row, the row count skips and the rank & window output works as a row number.

All support appreciated

Find more posts tagged with

Accepted answers

GrantSmith

Try using a formula tile to set the value of a column to be 1 if it's = 'market' and 0 if it's not (or whatever logic you need to mark it as the start). Then use the rank & window to do a running total of the column.