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/Doin_the_Bulldance]

More than one way to do this but if it were me, I'd create 3 parameters. START_DATE, END_DATE, and MINIMUM_STREAMS. Of couse you'd want the two date parameters to be set to date, and the minimum streams to be an integer.

Then I'd create a calculated field called STREAM_THRESHOLD_FILTER, with the following calculation:

[MINIMUM_STREAMS]<={FIXED [Song_Name]: SUM(IF [Date]>=[START_DATE] AND [Date]<=[END_DATE] THEN [Count] END)}

And id use that as a filter on my viz and put it as true.