"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.
@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.
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.
Thank you for your advise!!
Your advice inspired me! I could solve my question by using FILTER and appending them.