Creating a Cumulative Concat Function on a Text Field

cmoreno

I currently have an ETL looking at distinct Opportunities by their close won day. However, this issue is now looking at Distinct accounts within the same dataset. At first, I thought to use the count distinct function on the account ID in a groupby. However, since multiple opps can be tied to one account and each one can have different close dates, I know this would cause the account ID to be counted multiple times instead of once. Could it be possible to instead concat the Account ID by the Opp close won date and have it be cumulative as a list format to where it would list previous accounts as well? From there, I can use a count distinct function, and if that account ID is shown more than once, then it won't be counted twice. I know cumulative functions can be done in the rank and window tile for INT values, but this is a bit different.

DavidChurchman

If I understand correctly, you want a count of Account IDs by close date, but if an account ID appears for more than one close date, you only want them counted for a single close date.

If that's correct, I would split this into two parts, first figure out which repetition of the Account ID you want to include, and then count them by close date.

For example, if you only want to use the most recent close date for a given account ID, use a rank tile to rank the account IDs by descending close date, using account ID as the window. Then a filter tile for rank=1 to get only the most recent Account ID. (Or maybe you only want to include the close date with the highest value, you could use that to rank it).

From there, you could count your Account ID by close date using a group-by.

DavidChurchman

If I understand correctly, you want a count of Account IDs by close date, but if an account ID appears for more than one close date, you only want them counted for a single close date.

If that's correct, I would split this into two parts, first figure out which repetition of the Account ID you want to include, and then count them by close date.

For example, if you only want to use the most recent close date for a given account ID, use a rank tile to rank the account IDs by descending close date, using account ID as the window. Then a filter tile for rank=1 to get only the most recent Account ID. (Or maybe you only want to include the close date with the highest value, you could use that to rank it).

From there, you could count your Account ID by close date using a group-by.