仲間に相談

仲間に相談

ETL Rank&Windowでの累積列追加

Member
編集済: May 2023 仲間に相談

Dojo「仲間に相談」のトピックスでご指導いただき、ETLのRank&Windowで用いて累積列をもうけました。
月の順でSUMし、値が空白になったらその前までの累積したいです。
(列「Result」には0の場合は0と入りますので、空白になったらその後は必ず空白です)
Rank&Windowの④ではどのように設定すればよいでしょうか。

rank.png

 

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

こんにちは!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
ログイン

ベストアンサー

  • 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"

答え

  • @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"
  • 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.

  • 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"
  • Thank you for your advise!!

    Your advice inspired me! I could solve my question by using FILTER and appending them.

    ran3.png

こんにちは!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
ログイン

こんにちは!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
ログイン