HandsOn-AdvancedⅡ-復習まとめ

こんにちは。Riekoです。
今回はDATA Saber技術試練ord9、HandsOn AdvancedⅡについて復習したことをまとめていきます。
個人的な備忘録になりますので、丁寧語とタメ語が入り混じりることも踏まえ、駄文失礼いたします。

Q1

「最初の年と比べて何%増加していますか？」
という問いなので、表計算の「差の割合」を使うことができる。
ここで「基準」を「最初」にすれば、最初の年と比べて何%増加したかを見ることができる。

Q2

「都道府県を利益合計多い順に並べて、積みあがっていく状況と総計を表示してください」という問いなので、
ウォーターフォールチャートで実現することができる。
WFチャートができたらラベルの最大値を入れることで、利益が最大になる都道府県とその利益額を求められる。
地域特性については、地域を色に入れ、属性にすることで、地域ごとに色を分けられる。
ここでなぜ属性にするかというと、WFチャートに表すマークは、「都道府県ごとの利益」なので、ディメンションとして色に「地域」を入れてしまうと、「地域ごと都道府県ごと」の利益になってしまう。
地域を属性にすることで、集計に参加させず「都道府県ごとの利益」でマークを表示できる。
属性に関してはこちらのnoteを参考にさせていただきました。

Q3

「売上ランキングの推移」とあるので、ランクチャートを作る。
ところがランクチャートだと、どのサブカテゴリが１位をどれほどの期間獲得したのかわかりにくい。
そこで、年四半期と売上のランクを不連続にしてあげることで、セルごとにどれだけ１位を獲得したかわかるようになる。
不連続にしただけだと小さくてわかりにくいので、「MIN(1)」を入れてセルを大きくしてあげる。
サブカテゴリをテキストに入れれば、答えが導き出せる。
Q3-2については、カテゴリを色に入れて属性に入れてあげることで見えてくる。
なぜ属性にするかは、Q2と同じで、ディメンションのままだとカテゴリごと、サブカテゴリごとのランクになってしまうのに対し、カテゴリを属性に入れることで集計から除外し、サブカテゴリごとのランクにできる。

Q4

まず、利益TOP10顧客、利益WORST10顧客、そして結合セットを用いて、利益TOP&WORST10顧客のセットを作成する。
利益TOP10のセットを行に置き、TOP10とWORST10を分ける。
売上を列に持って行って、
「利益でTOP1の金額を出している顧客の売上とそれ以外の顧客それぞれの売上の差」なので、表計算の差を用いて、基準を「最初」にすることで、利益TOP1との差額を表すことができる。
ラベルの最大値を用いて、最も差が少ない金額を表示することができる。
その顧客が利益WORST何位かは、利益を不連続のランクにし、「昇順」を選択することで下から何位かを表示できる。ヘッダに持っていき、確認しよう。

Q5

「年四半期の売上が最高記録を更新したのは何回」という問なので、
表計算で最大値の累計を使う。
累計で最大値を選ぶということは、「売上が最大値であればそれを表示し、最大値でなければ前の値を維持する」というもの。
これはかなり悩んだので、クロス集計表を作成してみました。

合計売上と最大値累計のクロス集計表クロス集計表

すると、売上が最大値の時はその値が表示され、最大値でないときは前の値を維持されていることがわかる。
そして、最高記録を更新した値を出すには、以下の式を作成する。

INDEX()>1 and sum([売上]) = RUNNING_MAX(SUM[売上]))

最初の値は当然最大値になるが、厳密には更新した値ではないので、INDEX()>1で除外する。
最高記録を更新した四半期は、合計売上が累計最大値と一致した時（上記クロス集計表のピンクマークを付けたタイミング）なので、上記の式になる。

Q6

この問はダッシュボードを作って見ていく。
まず、サブカテゴリごとの利益率の棒グラフを作る。
次に、利益率を単体で棒グラフを作る。
２つのシートをダッシュボードに入れて、サブカテゴリごとの利益率をフィルターアクションに設定する。
WORST10サブカテゴリを選択して、利益率単体の棒グラフのシートに行き、「アクション」の編集で「除外」にすると、WORST3サブカテゴリを除外した利益率を見ることができる。
このままでは、元の利益率からどう変化したかわからないので、makeoverしていく。

Q6-LODを使う場合

{FIXED:[利益率]}

これを列に入れると、フィルターアクションに左右されない利益率を出せる。
これをメジャーバリューに入れ、メジャーねむーを色とサイズに入れる。
スタックマークをオフにして、色と透明度を調整すれば、2つの色とサイズで利益率の変化を確認できる。
スタックマークとは、積みあがった状態から積みあがっていない状態にする機能である。
スタックマークをオフにすることで、アクションの利益率とLODの利益率、両方を0からスタートさせることができる。オンにしていると積みあがってしまう。
スタックマークについてはこちらを参考にさせていただきました。

用語について調べてみる～スタック マーク用語について調べてみる～スタック マーク

Q6-セットを使う場合

サブカテゴリから適当なセットを作る。
利益率を列に入れて、サブカテゴリのセットを色に入れる。
ここでは選んだサブカテゴリを除外したいので、INを非表示にすることで、選んだサブカテゴリ以外の場合を表示できる。
続いてセットがかかっていない普通の利益率を列に入れ、二重軸にして、棒グラフにし、軸の同期をする。
メジャーネームをサイズに入れ、色を調節することで、2つのメジャーを重ねることができる。
ダッシュボードに作成したシートを配置して、ダッシュボードのアクションから「セット値の変更」を選び、「ターゲットセット」をサブカテゴリのセットにする。
LODで作成したシートのアクションがセットの方にも入っているので、ターゲットシートから抜く。
こうすることで、セット値を除外した値をセットアクションを使って棒グラフに表現することができる。

Q7

売上を行と列に置き、オーダー日を列に、サブカテゴリを詳細に置く。
行に置いた合計売上を、簡易表計算から「前年比成長率」を選ぶ。
こうすることで、年ごとのサブカテゴリの前年比成長率を見ることができる。
前年比成長率を色に入れてステップドカラーを2にすれば、前年比成長率が下がっているところと上がっているところの傾向を見ることができる。

「役員がモバイルで確認するため横に並べて各年を比較することができず、フィルターで切り替えて確認できるように依頼されています。」
のところは、フィルターに年を入れる必要がある。
ところが、前年比成長率なので、フィルターで該当年以外を除外してしまうと、前の値がないのでNULLになってしまう。

そこで、「表計算でオーダー年を取得する」ようにする。
すると、表計算フィルターはクエリパイプラインの最下位に位置するので、
前年比成長率なども取得し終わった後でフィルターすることができる。
具体的にはこの式を作成する。

LOOKUP(min(DATEPART('year',[オーダー日])),0)

DATEPARTはyearの部分を取得しているので2014や2016である。
その最小値なので当然2014や2016。
LOOKUPは列や行をずらす表計算で、0を指定しているので自分自身の値である。
つまりLOOKUPを使用することで、オーダー日の年を表計算で取得していることになる。
LOOKUPについてはこちらの記事を参考にさせていただきました。

Tableau Tips〜LOOKUP関数〜

こうしてできたオーダー年を不連続に変換してフィルターに入れると、フィルターを表示することができる。あとはダッシュボードに入れてスマートフォンサイズにすると完成。

Q8

出荷にかかる日数をDATEDIFFで算出する。
それを行に、オーダー日の連続の週を列に置くと週ごとの推移が見える。
行を複製して片方を円にすることで、UCLを超えた週に色をつけられるようになる。

「社の方針として上方管理限界線（UCL：Upper Control Limit）を3σとして管理しているのですが、Q8-1: この限界を突破して配送が遅延した週は何回ありましたか？」
については、標準偏差を用いて表現することができる。

標準偏差に指定する値は、データのばらつき度合いが標準偏差何個分に該当するかを入れます。

 -1,1を指定すれば、平均値から68%以内に収まる値
 -3,3を指定すれば、平均値から99.74%以内に収まる値

となります。
今回の場合「3σ」の範囲なので、-3,3を指定し、平均値から99.74%以内に収まるもの、あるいはそこから外れているものを可視化します。

分布バンドでUCL範囲を可視化できれば、次にUCLを超えた値について色を付ける。
UCLを超えた週については、以下の式を作成する。

AVG([出荷にかかる日数])> WINDOW_AVG(AVG([出荷にかかる日数])) + WINDOW_STDEV(AVG([出荷にかかる日数]))

先ほど「標準偏差〇個分」という表現をしました。
この場合は標準偏差3個分なので、*3を行い出荷にかかる日数の平均値に加えています。
出荷にかかる平均から標準偏差3つ分離れた値よりも出荷にかかる平均が大きければ、という意味です。
標準偏差についてはこちらの記事を参考にさせていただきました。

標準偏差とは？初学者向けに意味から求め方までわかりやすく解説

わかりやすくてとっても勉強になるのでお勧めです。
次に、Tableauにおける分布バンドの標準偏差についてはこちらを参考にさせていただきました。

Tableauの散布図を使って標準偏差からデータのばらつきを確認する | DevelopersIO

Q9

四半期ごとの売上を出し、オーダー年とメーカーで絞り込む。
メーカーのフィルターはワイルドカードの後方一致を利用して絞り込むことができる。
メーカーごとに売上のばらつきがあるので、「各行または列の独立した軸範囲」を選び軸を独立させる。
メーカーごとのトレンドを見るので、各売上についてはフォーカスがいかないように、軸を非表示にする。
また、あくまでトレンドということに着目すると、開始位置がバラバラでもいいので、軸の「ゼロを含める」にチェックを外すことでさらに傾向を見やすくできる。
ヘッダが消えてしまったので、数字がどのようになっているかを表すために「末尾」だけに数値を入れる。この際表示単位を区切ると見やすくなる。
末尾に数値を入れた際、どこに数値があるのかわかりやすくするために、末尾だけにマークを入れる。
そのためには、次の式を作る。

if last() = 0 then sum([売上]) end

最後だったら売上を表示する、という意味。
これを行に入れ、二重軸にし、円にする。軸の同期を行うと、末尾だけに円がつくようになる。

Q10

「地域別売上ランキング推移を年四半期で」とあるので、
年四半期ごとの地域売上ランクチャートを作る。
このままだと、間を除いて最初と最後でどう変動したかわからないので、表計算を使って最初と最後に絞る。

last() = 0 or first() = 0

こちらの計算式を使って最初と最後に絞ることで、最初の四半期と最後の四半期でどう変化したかがわかる。

Q11

家電と週数ごとの売上を出す。
パラメータで閾値を作成して、ブール値「閾値超過」を作成。

sum([売上]) > [閾値]

合計売上の指標なので、それが閾値を超えているか判定。
次に、閾値を超過していたら週数を足していく計算式を作る。

if [閾値超過] then PREVIOUS_VALUE(0) + 1 else 0 end

PREVIOUS_VALUEは、最初ならその値、2個目以降ならその前の値を返す表計算です。PREVIOUS_VALUEなら前の値からずっと保持できるので、この場合閾値を超えていれば、前の値にずっと1を足し、閾値を超えていなければリセットするという意味です。

続いて「連続で超えている間、500,000円の超過分を累計したとき、最も高い累計金額はいくらですか？」の問ですが、
「超過分を累計」というのがポイントです。
閾値を超えた値をどんどん累計していくのではありません。
式はこのようになります。

if [閾値超過]　then PREVIOUS_VALUE(0) + (sum([売上])-[閾値]) else 0 end

閾値を超過していれば、前の値に「売上と閾値の差分」を足し合わせる。
閾値を超過していなければ、0にリセットする、という意味です。
これをテキストに置けば閾値を超えている間の最も高い超過分の累計が出せます。
PREVIOUS_VALUEについてはこちらの記事を参考にさせていただきました。

【Tableau】PREVIOUS_VALUE関数を理解する | 株式会社プリンシプル

おわりに

覚悟していましたが、AdvancedⅡがやはり一番難しい…!
特にPREVIOUS_VALUEや標準偏差については、色々と調べてようやくわかりました。
まだまだ使いこなせているとはいいがたいので、これからも勉強し続け身に着けたいと思います。
ここまで読んでいただきありがとうございます。

Rieko