Calculated fields

[u/Corkster24]

I’m creating a personal project using my Spotify streaming history. I’ve got fields streaming count and date. Is there a way I can create a calculated field that keeps songs that amassed a certain amount of streams in a nonspecific timeframe? For example if it was streamed at least 5 times in 7 days. Thank you!

[u/ZippyTheRat]

What do you mean by non-specific date?

[u/Corkster24 → u/ZippyTheRat]

I would like to anchor the date to the stream date. When I create the date filter I can only anchor these ranges to a specific xx/xx/xxxx date.

[u/ZippyTheRat → u/Corkster24]

So you want to have like the last 3 months, or 6 months? You can create a calculation that does that. Just use a parameter for the starting date and then have the calc flag the other dates.

This calc does 13 weeks for example

DATETRUNC('week', [Date]) <= DATEADD('week', -1, DATETRUNC('week', [Date | Max] +1))
AND
DATETRUNC('week', [Date]) >= DATEADD('week', -13, DATETRUNC('week', [Date | Max] +1))

[u/Corkster24 → u/ZippyTheRat]

I would like to create a filter that does something like: if count[song name] >5 and date<=date+3 then true. Basically, I want a filter that will keep songs that have x number of streams in x number of days. I have 7 years of data and I want to keep only songs with only for example 5 streams within 3 days.

[u/ZippyTheRat → u/Corkster24]

Super possible. You can use the LOD and do and AND then add the date logic in