累積グラフ（Running total Grouped)で翌月以降を非表示にする方法

eriena

累積棒グラフにおいて、売上個数の予測は年間表示するが実績が途中までしかない場合、累積棒グラフで表示すると期中の実績以降の累計が同じ値で表示されてしまいます。そのため実績がまだない期間について棒グラフを表示しないように設定することは可能でしょうか？

 

 

例えば、いま4月だとして、5月以降の実績がないので、添付エクセルのような表になっているとします。

 

こちらの表を　Chart type で　Running total Groupedチャート　にしますと、予測と実績が両方とも年間分表示されてしまいます。

 

 

それを、実績だけ当月までの累積表示にしたいです。

（下図は図形で棒グラフを隠したりして修正してみました。このような図にしたいです↓）

 

 

以前、Dojo投稿で読んだ内容も試してみました。

①#当日以前フラグ　というBeastmodeを追加して　case when `month` <= CURRENT_DATE() then 1 else 0 end という構文をいれ

②Filterで　　#当日以前フラグ　＝　1

しかし、実績だけでなく予測まで当月までの表示になってしまいました。

 

 

予測は1月から12月まですべて表示、実績だけ当月までの表示とする場合は、どうすればよいでしょうか？

 

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

 

 

 

jaeW_at_Onyx

MagicETL does support Window Functions which would allow you to create a cumulative total for a year-to-date calculation.

 

If you want something like a percent of total calculation, you would use GROUP BY and then JOIN the grouped data back to the main data.

 

 

I would recommend that you AVOID aggregate functions like these in your ETL because they don't respond to filters.  

If I needed to do projections based on a moving average or cumulative calculation, I'd see if I could do it using the new data view beta feature.  Ask your Domo representative for more information

 

With a Data View, you could accomplish the alculation using the technique described in this video.

https://www.youtube.com/watch?v=ZPf41Fjn1H8&list=PLUy_qbtzH0S4CkHBUvpOVpLNJluk6upOn&index=11&t=4s

 

 

moffari

複雑なETLを使っても良さそうな雰囲気なので、

GWのお勉強として、ETL DataFlowのみで実現する方法を試作して、まとめてみました。

こういうときは、SQLの方がテキストのみで伝えられるので、楽かもしれませんね。

 

画像を縮小したり、詳細な補足が足りないので、

わかりづらいところがあるかもしれませんが、

画像は100％表示にすれば、なんとか読めると思います。

 

もっとスマートな回答があるかもしれませんがご容赦ください

 

 

 

●パターンA：累計は1月～12月とする場合

累計期間が1～12月の場合のDataFlow

カード化Result(年間累計)のBeastMode

●パターンB：累計は4月～3月にする場合（パターンAからの差分のみ）

累計期間が4～3月の場合のDataFlow

不明

@eriena  it's just Jae ? don't worry.

 

in SQL a Frame describes "for each row, what are the range of values to consider in the RANK / WINDOW"

 

In your case you said you wanted a cumulative function (Apr = Apr, May = Apr + May).  In that case the preceding rows / range should be unbounded, and the following rows / range should be 0.

 

moffari

@erienaさん

 

私が似ている項目名でサンプルを作ったため、誤解されているかもしれません。

【変換A】の出力データセットは添付Excelのようになっていると思います。

そして、BeastModeで新規項目「A」を定義します。

 

 

最後に、カード化する際「系列」に「A」を紐付けます

 

 

これで、凡例の名称が変わってしまいますが、グラフはご期待の結果になると思います。

いかがでしょうか？

moffari

@eriena さま

 

「実働3日目」の定義が見えなかったので、

「毎月3日」で区切ってよければ下記で対応可能です。

 

(CASE 
	WHEN DAY(CURRENT_DATE()) <  3 AND `Month` <= DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH) THEN `Result(年間累計)`
 	WHEN DAY(CURRENT_DATE()) >= 3 AND `Month` <= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) THEN `Result(年間累計)`
 	ELSE 0
END)

 

eriena

追記させてください。

Dojo日本支部内過去のQ&Aを確認し、BeastModeにCase文をいれてソートしたところ、予算も実績も両方とも当月以降非表示になってしまいました。予算は４月―３月で表示、実績は４月ー当月までを表示させるのは難しいでしょうか。

moffari

やりたいことは痛いほどよくわかるのですが、

作図ツールではないので、提示された条件から

既存のパーツを使ってボタン一つで実現する方法は現状ないと思います。

 

その前提で、提案するとすれば、

 

①見た目最優先の場合（予測は年間分を表示して、実績は当月までを表示する形式）

　計画も実績も累計の集計し終わったデータを用意すれば、BeastModeで少し加工すれば

　見た目だけなら実現できます。

 

②年度末の予測に対する進捗の見える化が実現できればよい場合

　→予測も実績も当月まで(okochierienaさんの3つめのキャプチャの状態)を許容できるなら、

　　年間の最終予測値を「目盛りマーカー」を使って、表現するなどはいかがでしょうか。

 

当初やりたかったことからズレた提案かもしれませんが、ご参考まで。

eriena

お返事頂きましてありがとうございます！

 

「目盛りマーカー」で最終予測値を常においておく方法があるのですね！

今回は予測を月単位で年間で見るのも目的のひとつなので使うことができませんが、今後のカード作成に役立てたいと思います。

 

既存のパーツでカード上表現する方法が無いことがわかりました。

 

結果、累計の値を持ったデータセットを準備しようと思います。

MySQLですと累計の値を持たせることはできるそうですが、データセットはETLで作成しているので、累計の値が算出できません。（ググっても良い方法を見つけられませんでした）

 

ETLで累計の値を出す方法についてもし良い方法があれば教えてください。

 

 

 

moffari

「MySQLなら出来そう」とおっしゃっているので、

今回準備しているデータセット(最初の投稿の book.xlsx)に対し、

新しいDataFlow(SQL)を作成することで、

累計結果のデータセットを作ればいいと思いますが、駄目なんでしょうか？

 

①今準備できてるデータセット→②DataFlow(MySQL)→③累計結果のデータセット

 

入り口は複数ありますが、対象のデータセットを開いて、

添付画像赤枠部分からDataFlow(MySQL)を作れます。

 

eriena

お返事ありがとうございます！

MySQLだとできそうなのですが、ETLで完結したいというこだわり（すみません！）を捨てきれずにいます。

jaeW_at_Onyx

MagicETL does support Window Functions which would allow you to create a cumulative total for a year-to-date calculation.

 

If you want something like a percent of total calculation, you would use GROUP BY and then JOIN the grouped data back to the main data.

 

 

I would recommend that you AVOID aggregate functions like these in your ETL because they don't respond to filters.  

If I needed to do projections based on a moving average or cumulative calculation, I'd see if I could do it using the new data view beta feature.  Ask your Domo representative for more information

 

With a Data View, you could accomplish the alculation using the technique described in this video.

https://www.youtube.com/watch?v=ZPf41Fjn1H8&list=PLUy_qbtzH0S4CkHBUvpOVpLNJluk6upOn&index=11&t=4s

 

 

eriena

Hi Mr Jae,

Thank you for your kind reply.

I would like to set cumulative value by Rank&Window.

What value should be set in "What frame range should be used? " to create new column to accumlate forecast value as like Apr = Apr, May = Apr+May ...

 

--(日本語)--

Rank&Windowで累積の値を設定しようと思います。

しかし、What frame range should be used? の意味がわかりません。

Forecastの累積の値を作成する場合は、何を入れればよいのでしょうか？

累積の値には、AprではAprの値、MayではApr+Mayの値を入れたいです。

 

Thank you.

moffari

複雑なETLを使っても良さそうな雰囲気なので、

GWのお勉強として、ETL DataFlowのみで実現する方法を試作して、まとめてみました。

こういうときは、SQLの方がテキストのみで伝えられるので、楽かもしれませんね。

 

画像を縮小したり、詳細な補足が足りないので、

わかりづらいところがあるかもしれませんが、

画像は100％表示にすれば、なんとか読めると思います。

 

もっとスマートな回答があるかもしれませんがご容赦ください

 

 

 

●パターンA：累計は1月～12月とする場合

累計期間が1～12月の場合のDataFlow

カード化Result(年間累計)のBeastMode

●パターンB：累計は4月～3月にする場合（パターンAからの差分のみ）

累計期間が4～3月の場合のDataFlow

不明

@eriena  it's just Jae ? don't worry.

 

in SQL a Frame describes "for each row, what are the range of values to consider in the RANK / WINDOW"

 

In your case you said you wanted a cumulative function (Apr = Apr, May = Apr + May).  In that case the preceding rows / range should be unbounded, and the following rows / range should be 0.

 

eriena

ユーザー08249さま

ご返信ありがとうございます。

カードが私の表現したいようになっていました。ありがとうございます！

ETLは確認し自分でも同じことを試していますが自分の理解が足らずまだ思うように作れません。

引き続き、頂いたETLを再現できるように進めていきます。

eriena

@jae_atOnyx　

Hello Jae,　

Thank you for your reply.

I would like to know to create two accumlated colums as marked yellow in attached excel file.

And if today is May, there is no value in the columns of "Result" in Jun to Dec. Therefore I want to set no value in the colums of "Result(Accum)" in June to Dec.

Is it possible to create such colums by just set Rank&Window in ETL ?

 

--Japanese--

添付エクセルにて、ETLのRank&Windowを設定することで、黄色くした累積列を追加することは可能でしょうか。但し当月が５月の場合、6月―12月にはResult列に数値が入らないので、6月―12月のResult(Accum)にも数値が入らないようにしたいです。

eriena

教えて頂いたETLとカードを実践してみましたが、カード上でResult(年間累計)のBeastModeを設定しても、どうしても6月以降にも累計の値が出て5月~12月まで同じ値が棒グラフで表示されてしまいます。【変換A】の出力データセット上では6月以降もResult（累計）に値が入っていると思うのですが、どのようにしてカード上で当月より後を表示させていないのでしょうか。

moffari

@erienaさん

 

私が似ている項目名でサンプルを作ったため、誤解されているかもしれません。

【変換A】の出力データセットは添付Excelのようになっていると思います。

そして、BeastModeで新規項目「A」を定義します。

 

 

最後に、カード化する際「系列」に「A」を紐付けます

 

 

これで、凡例の名称が変わってしまいますが、グラフはご期待の結果になると思います。

いかがでしょうか？

eriena

@moffari さま

 

分かりやすくAの凡例にしてくださりありがとうございました！

私の読み取る力がなく大変失礼いたしました。よく理解いたしました。

また、私のほうでも同じように設定し、当月より後を表示させないカードを作ることができました。

このような設定が可能であることがわかりとても嬉しいです。

 

もう1点質問させてください。今は5月である場合、5月の実績はまだないので、4月までのResult(累計）を表示させたい場合、どのようなBeastmodeの設定が可能でしょうか。

前月の実績が出るのが当月の実働３日目であるのが前提として、具体的には、

6月実働2日目までは2020年4月1日までのResult(累計）を表示させておく、

7月実働2日目までは2020年5月1日までのResult(累計）を表示させておく、

8月実働2日目までは2020年6月1日までのResult(累計）を表示させておく、

というような設定ができればと思っています。

逆に言えば

6月実働3日目からは2020年5月1日のResult(累計）を表示させておく、

7月実働3日目からは2020年6月1日のResult(累計）を表示させておく、

8月実働3日目からは2020年7月1日のResult(累計）を表示させておく、というようにしたいです。

 

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

moffari

@eriena さま

 

「実働3日目」の定義が見えなかったので、

「毎月3日」で区切ってよければ下記で対応可能です。

 

(CASE 
	WHEN DAY(CURRENT_DATE()) <  3 AND `Month` <= DATE_SUB(CURRENT_DATE(), INTERVAL 2 MONTH) THEN `Result(年間累計)`
 	WHEN DAY(CURRENT_DATE()) >= 3 AND `Month` <= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH) THEN `Result(年間累計)`
 	ELSE 0
END)

 

eriena

@mofarri　さま

 

また解決してくださりありがとうございました！

うまくいきました！