How to filter a Calculated Colums with a Partition in Datasphere

[hleboeuf]

Hello,

I'm a novice in DS, working for years in Azure and Informatica, so searching my way.

I have a issue in a view where I first create a calculated column like ROW_NUMBER() OVER (PARTITION BY CUSTOMER), its called xROW, until there works fine.

In the next 'block' I want to filter only the 1 values, but there I'm getting the error

The expression is using a calculated column which uses window function.

What is right, but why can't I filter on that column ? I would pfrefer to keep it in a graphical view as on long term we might involve more people and their SQL-coding skills are not that high.

[Simon_Ye]

You have two options:

Assuming we want to get the first day of each month from the standard SAP time dimension:

Option 1: FIRST_VALUE function:

FIRST_VALUE(DATE_SAP) OVER (PARTITION BY CALMONTH ORDER BY DATE_SAP)

Option 2: RANK function:

RANK() OVER (PARTITION BY CALMONTH ORDER BY DATE_SAP)

Those two functions just return different values, but you can set the filter on any of them to filter the records, here I only set the filter on "Day Rank in Month" because data preview doesn't support filters like "Date(String)" = "First Day of Month".