How to get the trailing 12 month but not include the current month

Q&A
, ,
1

I want to calculate the sum of past 12 months but not include the current month, and I tried to use windowsum function. However, in windowSum function,
the start index is a positive integer, indicating n rows above the current row, while the end index is a positive integer, indicating n rows below the current row.
image

E.g., windowSum(sum(monthly quantity),[month ASC],12,0) would cover the past 12 month and the current month, but what if i only want past 12 month, not include the current month. let’s say for month Oct 2024, i want the sum of value from Oct 2023 to Sep 2024. Is there a way to do it?

2

Hi

you can try this and change the field names accordingly.

truncDate(‘YYYY’,addDateTime(-dateDiff(truncDate(‘YYYY’,{arrival_timestamp}),now()),‘DD’,{arrival_timestamp}))

This would be to get the distinct Count if it’s this year.
avg(distinctCountOver(ifelse({last tweleve months}=addDateTime(-1,‘YYYY’,now()),{session_id},NULL),[{last tweleve months}],PRE_AGG))

Let me know if that works for you.

3

Hi Shahid,

thanks for replying! Could you please help clarify what session_id here means?

4

I realize I could have just used
windowSum(sum(monthly quantity),[month ASC],12,0) - windowSum(sum(monthly quantity),[month ASC],0,0)
the last 12 months - the current month

1 Like
5

Hi @yuchen1

Apologies for being late reply!

Would it work for you or not?

6

Hi @yuchen1,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

7

Hi @yuchen1,
Since we haven’t heard back, I’ll close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!