Hello! Is there a way to get continuously trailing 3 month counts? For context, I’m trying to build a time series line graph that shows % of active users for the past 3 months inclusivecontinuously. For example, if July shows 25%, that means 25% of users were active in July and/or in 3 months before July. I’m trying to use truncDate of my datetime field (session_started_at) to get the first of each month to compare to (Session Month). For some reason, my graph looks different: it’s hovering between 80%-100% on average monthly, while a snapshot 3 month count using now() is showing about 43%. Here’s my current time series query:
distinct_count(
ifelse(
{is_proper_session} = 1
AND isNotNull({team_lead_id})
AND {session_started_at} >= addDateTime(-2, 'MM', {Session Month})
AND {session_started_at} < addDateTime(1, 'MM', {Session Month}),
{team_lead_id},
NULL
)
)
/ distinct_count({team_lead_id})
So is ‘Session Month’ a calc. field that just captures the month?
If your percentages are showing on the higher side, it leads me to believe that it has something to do with your denominator. For each prior month, wouldn’t you want the percentage to be based off of each month’s count as opposed to the overall count?
I’m trying to ultimately construct a time series that shows rolling 3 month data throughout. My data is also dependent on count based on condition, i.e. in rolling 3 months, count the number of item A based on condition B occurring within the rolling 3 months, which should also include historical counting. For example, Nov 1 should show % of data from the past 3 months (starting Aug 1), and Aug 1 should show % of data from that past 3 months (starting May 1). I think I have trouble understanding how to create a calculated field that accomplishes this, and the query I provided above does not appear to produce the correct data I want to see. I’ve also been trying to use parameters, but I don’t think my parameter (screenshot below) is producing historical rolling 3 months.
Following up here as it’s been a while since last communication took place on this thread; Were you able to see Brett’s most recent most and/or find a solution yourself in the meantime?
If we do not hear back within the next 3 business days, I’ll close out this topic.
