Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Check out our powerful new search update! You can read more here. Please let us know if you have any feedback by creating a topic here.

Moving Sum calculation in-DB

Highlighted

Hi Guys,

I am using Snowflake and trying to create a moving sum in-DB of the current record and 3 previous records. i.e a 4 week sales number.

Any ideas how I can do this In-DB?

 

Thanks!

 

 

Highlighted
Alteryx Certified Partner

Hi,

 

You have already tried the summarize tool. If you have a key to aggregate sales values ​​just add the values ​​through the summarize tool. If you have questions clarify your problem further

 

clipboard_image_0.png

Thanks for the reply. I already have sales summarized by week. I am trying to get a new column that sums the 4 weeks sales up to and including that week. Please see below for an idea of what I am trying to achieve.
Highlighted
Alteryx Certified Partner

Hi,

 

If the amount of information you are working on IN-DB is not too large you can use the in-out Multi-Row-Formula tool. The problem you will have to get out of the IN-DB ambienge. Da to do a parameterized sql to generate this calculation on extraction. Here's an example with the multi row tool.

Highlighted
Thank-you. I am able to perform this task out of DB, however for this use case I need to do this in DB. Is there no way to do this?
Highlighted
Alteryx Certified Partner

Hi,

 

The query below can be used either directly from the IN-DB Connection tool or from the dymamic input in-DB tool. It may be an idea for the new appointments.

 

select WEEK as WEEK,
max(SALES) as SALES,
sum( (select sum(SALES) from TABLE_SNOWFLACK B where B.week between (A.WEEK -3) and A.WEEK))as SALESWEEK4
from CTABLE_SNOWFLACK A
where WEEK between (A.WEEK -3) and A.WEEK
group by WEEK
order by WEEK

 

[]

Labels