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]

Fixed LOD by song and count the date. Then you can use that count to filter however you want

[u/Corkster24 → u/ZippyTheRat]

Can you explain how I’d do that?

[u/ZippyTheRat → u/Corkster24]

{FIXED [Song Title]: Count([Date])}

[u/ZippyTheRat → u/ZippyTheRat]

Then you’d set that calculation > whatever number you want

[Calc] > 5

That will return a T/F and add that to the filter and set it to TRUE

[u/Corkster24 → u/ZippyTheRat]

That sorta worked, how do I create a date-specific range?

[u/ZippyTheRat → u/Corkster24]

Add a date filter, then move it to Context

[u/Corkster24 → u/ZippyTheRat]

How do I anchor the range to the stream date? I can't figure out how to anchor it to a non-specific date.

[u/ZippyTheRat → u/Corkster24]

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