I need help writing logic in calculated column to give results below. ID date ProdFlag NormTime XYZ1 8/31/2023 1 3 XYZ1 8/31/2023 1 3 XYZ1 7/31/2023 1 2 XYZ1 7/31/2023 1 2 XYZ1 7/31/2023 1 2 XYZ1 7/31/2023 1 2 XYZ1 6/30/2023 1 1

[Kate E 2]

I need help writing logic in calculated column to give results below.

 

ID date ProdFlag NormTime

XYZ1 8/31/2023 1 3

XYZ1 8/31/2023 1 3

XYZ1 7/31/2023 1 2

XYZ1 7/31/2023 1 2

XYZ1 7/31/2023 1 2

XYZ1 7/31/2023 1 2

XYZ1 6/30/2023 1 1

[Kate E 2]

For each ID and Date, I want to count production flag for normalized time but ONLY count once for each time and date.There is a data type column indicating production or forecast but I want to count for both so that can't be used as a filter.

[Gaia Paolini]

Did you mean to show the desired result? I am not sure I understand.

[Kate E 2]

Thank you for reply. I added table as an example output for the normalized time calculation. I want to calculate a column which calculates normalized time but ONLY counts it once per individual date and ID. I.e. the table result example but this will be for hundreds of thousands of rows.

[Kate E 2]

I tried making a ubique key adding the ID and the prod date and a case when prod flag >0 then unique count the unique key over all previous intersect date and ID but it freezes when calculating ( does not calculate after hours)

[Kate E 2]

unique*

[Gaia Paolini]

sorry I really still do not understand what you want to calculate. The table shows four columns. Is one of them the desired result? It looks like you want to calculate NormTime but you say you need to calculate the new column 'once for each time and date'. So time is already known?

[Kate E 2]

Yes- normalized time is the calculated column. I was just giving the specific notes as to how that should be calculated.

I have raw data of ID and Date. I calculate the production flag( no issues ) and want to calculate normalized time column which would result in results consistent with the table.

[Jose Leviaguirre]

Will this help to create a new column called NormTime?

If(
    DenseRank() Over (Partition By [ID] Order By [Date]) = 1,
    DenseRank() Over (Partition By [ID] Order By [Date]),
    DenseRank() Over (Partition By [ID] Order By [Date])
)

NormTime should increase by 1 for each unique combination of ID and Date.

DenseRank assigns a rank to each unique combination of ID and Date based on the order of Date..If the rank is 1 for a particular combination, it assigns that rank as the NormTime value. And if it is greater than 1, assigns the same rank as 'NormTime' increases by 1

[Kate E 2]

I like suggestion but not seeing partition as a function in spotfire?

[Jose Leviaguirre]

Hello Katye, it was a way to illustrate the concept as I do not have enough data to work with. Try something like. or attach a larget sample dataset with desired output

DenseRank() Over ([iD], [Date])