Sum distinct of previous records with condition

[varenich]

Hi,

Could you please advice how to make an expression or script to sum groupped distinct values with additional condition?

In each row of a segment I need to sum distinct (UniqueID) values (CRITICAL) from the row to begginning of a segment with additional check if UniqueIDEnd (of each previous record)  >[Check time] (of the current record)

Tried tons of ways, broke my head but  without success. Please help.

[mrybalko]

Hello

Could you explain segmentation conditions? What is segment?

[varenich]

Just Date (left column)

[mrybalko]

Ok. For every row we need to calculate the cumulative sum(critical) for distinct UniqueIDs with additional time check, don't we?

What value should be displayed in rows with duplicate UniqueID: just repeat the previous value, null or exclude this row?

[sunny_talwar]

What is the significance of the red line going across? Is that saying that 511 will only be summed for rows above the red line? If so, why? Also, it would help if you can share the expected output in an Excel file.

[varenich]

[varenich]

Please check screenshot below

[varenich]

Tried "As Of" tables, but it hangs up my computer. I think its because I have 150 000 * 20 unique records per day.

[mrybalko · Accepted solution]

I think that is it

[varenich]

Wow! How did you do it?!

Let me a minuite to check the script, please