ETL Rank&Windowでの累積列追加
Dojo「仲間に相談」のトピックスでご指導いただき、ETLのRank&Windowで用いて累積列をもうけました。
月の順でSUMし、値が空白になったらその前までの累積したいです。
（列「Result」には0の場合は0と入りますので、空白になったらその後は必ず空白です）
Rank&Windowの④ではどのように設定すればよいでしょうか。
よろしくお願いいたします。
ベストアンサー
-
"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"1
答え
-
@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"1 -
Thank you for your reply.
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.0 -
"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"1 -
Thank you for your advise!!
Your advice inspired me! I could solve my question by using FILTER and appending them.
0