# ETL　Rank&Windowでの累積列追加

オプション
Member

Dojo「仲間に相談」のトピックスでご指導いただき、ETLのRank&Windowで用いて累積列をもうけました。

（列「Result」には0の場合は0と入りますので、空白になったらその後は必ず空白です）
Rank&Windowの④ではどのように設定すればよいでしょうか。

よろしくお願いいたします。

## ベストアンサー

• Coach
回答済み✓
オプション

"But if no value (as like null), I want to accumlate up to the previouse month and don't want to accumlate after that month."

OH.  So you have the exact opposite problem I described.

Create a FILTER WHERE Amount is not NULL.  Calculate the cumulative SUM.  then APPEND that data back to the rows where Amount IS NULL.

Alternatively you could calculate the Max_Date where Amount is Not NULL and filter your data on Max_Date and then do your cumulative Sum.

Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨‍💻

**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

## 答え

• Coach
オプション

@eriena , i'm sorry, google translate is doing a horrible job translating your question.

I'm assuming you're trying to create a cumulative sum and want to include one row per month even in months where there is no data.

The cumulative sum (WINDOW & RANK) tile cannot generate months for you if there is no data, so you must LEFT JOIN your transaction table to a date dimension that has one row per month.

you could use a date  dimension, apply a GROUP BY month transformation.  and then LEFT JOIN to your transactions.

That way you guarantee a row per month.

Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨‍💻

**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
• Member
オプション

I'm not good at translating, but I let me try.
I'm trying to accumulate the value of "Result" into "Result(Accum)" in order from Jan to Dec.
If the value in "Result" >= 0, I want to accumlate in order.
But if no value (as like null), I want to accumlate up to the previouse month and don't want to accumlate after that month.
Is it possible?
I try to set "null" as like this, but I couldn't solve.
I need to accumlate several colums at once, so I can't use LEFT JOIN.

• Coach
回答済み✓
オプション

"But if no value (as like null), I want to accumlate up to the previouse month and don't want to accumlate after that month."

OH.  So you have the exact opposite problem I described.

Create a FILTER WHERE Amount is not NULL.  Calculate the cumulative SUM.  then APPEND that data back to the rows where Amount IS NULL.

Alternatively you could calculate the Max_Date where Amount is Not NULL and filter your data on Max_Date and then do your cumulative Sum.

Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨‍💻

**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
• Member
オプション