Keep data in column as constant regardless of date

EChampagne · October 29

I have an ETL that includes a weekly upload of new information (employee name, quota, current sales) that is batched on Mondays. The quota is typically the same from week to week. When I use the information in a dashboard looking at last week, it doesn't recognize the quota since it's from the current week. Is there an ETL or Beast Mode Calculation that I can use to say regardless of date, use the quota form recent upload?

ColemenWilson · October 29

So you want to compare the current quota, to historic sales data? If that is the case you can just join your quota by rep and then for every row of sales data the quota will join to it. You'd have something like this:

Date	Rep	Sales	Quota
1/1/2024	John	20	30
1/2/2024	John	25	30
1/3/2024	John	20	30
1/4/2024	John	23	30
1/5/2024	John	24	30
1/6/2024	John	25	30
1/7/2024	John	20	30

ColemenWilson · October 29

Yes. In your ETL you can use a rank and window tile to assign a rank to the most current quota for each employee by date. It would look something like this:

1.

2.

3. Then in your card add a filter for the Rank Value field and set it to Rank Value = 1. This will keep the row of data with the most recent update date.

Let me know if you get stuck!

EChampagne · October 29

I'm not sure that's what I'm looking for. I want the quota to be a measure that I can use to create beast mode calculations within the cards.

Is there another way?

ggenovese · October 29

Can you post a sample of your data? It sounds as if you're keeping prior weeks sales data but not the prior weeks quota, is that an accurate assessment?

ColemenWilson · October 29

The quota field would remain, but previous quota values would be filtered out and you'd be left with the most recent quota amount.

EChampagne · October 29

Yes, that's correct. My 'quota' (which below is the "visits agreed upon" column) is always going to be associated with the current week (10/28 below); however, I need it to align with the other weeks. Is there a way to do this without using append?

ColemenWilson · October 29

So you want to compare the current quota, to historic sales data? If that is the case you can just join your quota by rep and then for every row of sales data the quota will join to it. You'd have something like this:

Date	Rep	Sales	Quota
1/1/2024	John	20	30
1/2/2024	John	25	30
1/3/2024	John	20	30
1/4/2024	John	23	30
1/5/2024	John	24	30
1/6/2024	John	25	30
1/7/2024	John	20	30

EChampagne · October 29

Can you explain the specific join that should be used?

ColemenWilson · October 29

In an ETL, isolate your quota data and filter so you only have 1 row per rep, per quota. Then join that back to all data on Rep = Rep. If you provide sample data with Rep, Quota, and sales amount fields I can build it in an ETL and show you.

Keep data in column as constant regardless of date

Best Answer

Answers

Categories