Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Moving Sum calculation in-DB

5 - Atom

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

Highlighted
5 - Atom
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
5 - Atom
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