オリンピック、おそるべし XX県！ 驚きの金メダル率
もうちょっとタイル地図を使ってみたくなり、別のを作ってみました。
高校野球とか、大臣出身地とかでもそうですけど、自分の出身県の成績とかランキングって何か気になってしまいますよね。。
ちょうど、タブローではオリンピックねたが扱われるようになって来てるので、今回は、都道府県出身者別、メダル獲得数を作ってみました。
大して意味はないといえば意味はないのですが、私としては結構、驚きの結果が。。
先に簡単なグラフの作り方
正解だけ気になる方は、一番下まで一気に移動願います。。。
作り方ですが、各県にColumn(列）と Row（行）の番号を割り振って配置するだけです。少し前に、カレンダーでやったのと基本的に同じやり方です。
カレンダーでは、なるべく「IF」使いたくなかったですけど、今回は特に位置関係と都道府県のコードには関連がないので、IF 的要素を使います。
が、IF ではなく、「Case+When」 を使います。
「IF」よりも、見た目が整理されています。
さて、公開してあるタイル地図に用いた配置は以下の通りです。
[Row ID]
case [Pref Code Int]
when 1 then 1
when 2 then 2
when 3 then 3
when 4 then 4
when 5 then 3
when 6 then 4
when 7 then 5
when 8 then 6
when 9 then 6
when 10 then 6
when 11 then 7
when 12 then 7
when 13 then 8
when 14 then 8
when 15 then 5
when 16 then 6
when 17 then 5
when 18 then 6
when 19 then 7
when 20 then 7
when 21 then 6
when 22 then 8
when 23 then 7
when 24 then 8
when 25 then 7
when 26 then 7
when 27 then 8
when 28 then 7
when 29 then 8
when 30 then 9
when 31 then 7
when 32 then 7
when 33 then 8
when 34 then 8
when 35 then 8
when 36 then 11
when 37 then 10
when 38 then 10
when 39 then 11
when 40 then 9
when 41 then 9
when 42 then 9
when 43 then 10
when 44 then 10
when 45 then 11
when 46 then 11
when 47 then 12
else 12
end
[Column ID]
case [Pref Code Int]
when 1 then -1
when 2 then -2
when 3 then -2
when 4 then -2
when 5 then -3
when 6 then -3
when 7 then -2
when 8 then -1
when 9 then -2
when 10 then -3
when 11 then -2
when 12 then -1
when 13 then -2
when 14 then -3
when 15 then -3
when 16 then -4
when 17 then -5
when 18 then -6
when 19 then -3
when 20 then -4
when 21 then -5
when 22 then -4
when 23 then -5
when 24 then -6
when 25 then -6
when 26 then -7
when 27 then -8
when 28 then -8
when 29 then -7
when 30 then -7
when 31 then -9
when 32 then -10
when 33 then -9
when 34 then -10
when 35 then -11
when 36 then -9
when 37 then -9
when 38 then -10
when 39 then -10
when 40 then -13
when 41 then -14
when 42 then -15
when 43 then -14
when 44 then -13
when 45 then -13
when 46 then -14
when 47 then -15
else -1
end
Column でマイナスを使っているのは、基本設定は上から下、左から右にソートされるので、沖縄が 1 になるようにしても良いのですが、少し問題が。。
都道府県コードは北海道から順につまり、北から南、東から西に順番がつけられています。ですので、上から下、左から右 という順番は、少なくとも日本の都道府県コードとマッチングさせるには、扱いにくいものとなります。
ですので、マイナスを頭につけることで、絶対値が小さい都道府県ほど右（東）であるという認識が持てるようにしています。
あとは、今回はデータに、どの都道府県にもマッチしないアイテムが入っていましたので、それらは左下、座標 「12,-1」に表示させるため、else 以下のTextを追加しています。
あとは、
都道府県の色が、全部のメダル獲得数。中の棒グラフがそれぞれのメダルの獲得数で、2軸を使うため、いくつか表示の工夫が必要です。
今回は、「銅メダル」上のみに都道府県名表示がされるように工夫しました。
さらに、メダルが「０」の県もあったので、これらは、仕方なく、Dummy Dataを追加しました。必要悪？です。
具体的には、47都道府県ｘ金、銀、銅で合計141個のDummy Dataを追加したしています。
これにより、さらに例外回避も必要になってきましたが、詳細は省きます。
タイトル煽ってますよね？
で、どこの県よ？
ってことで、獲得したメダルが「金」ばっかりって県は３県あるのですが、山形、山梨はそれぞれ1個ずつ、それに対して、
奈良県です。
ちょっと驚きませんでしたか？
奈良県をクリックしてもらったら、下に表が出てきます。
何のことはない、柔道、野村忠宏 さんによる3連覇が利いています。しかし、その下にもう一人野村姓が。。。
テーブルの行をクリックすると、ハイパーリンクが現れるので、それをさらにクリックすると、Wiki に飛ぶようになっています。
このあたりは、Dashboard の Action という機能で実現できます。
技術的には簡単なので、タブローのHPでもすぐに見つかります。手間はかかりますが、まあまあ面白いです。
実は、野村豊和さんはお父さん、かと思ったのですが、Wiki によると、伯父様だそうです。それにしてもすごい家系ですね。
ってことで、奈良県民の皆様、（っていうか、ご本人たちはよくご存知かも？！）
大いに自慢してもらっていいと思います！！！ 海はないけどな。
ダッシュボードについての説明は、
これとか、
ダッシュボードのアクションを使用したフィルターの作成 | Tableau Software
これですね。
それでは、ご自分でトライして楽しんでください。
これっすよ。作りたかったグラフ －タイルで日本地図ー
以前から気になっていて、トライしてみたかった地図グラフがありました。
似たようなのは、アメリカ版では見たことがあるのですが、これとかですね。
（画像リンク注意）
面白そうだからやってみたかったんですよ。 日本版。
トライしてみました。
こんな感じっす。
はっきり言って、これだけだと、普通の地図に、県ごとに色つければいいんです。付加価値はありません。見た目がちょっと変わってるくらいのことで。。
しか～し、こっちになると、ちょっと付加価値が出てきます。
各 県ごとのグラフが、おおよその県のロケーション上に表示されてます。
地図を簡便化するのって、失敗すると全然ダメですが、作りようによっては面白いものが出来るように思います。
何かを犠牲にしないとうまく簡便化できないので、上図で意識したのは以下のことです。
- 海岸線のつながり。
海のない県を意識しました。海のない県はどの辺も海に面していないハズ。
- 特徴的な地理条件
- 東京の位置
関東はややこしいです。（あんま知らないし）
が、海外の方は、東京の位置がずれすぎてると混乱するでしょうから、できるだけ、東京の隣接県はキープするようにしました。
ただ、海のない県を外に持ってくることはしませんでした。
結果、千葉、茨城と隣接させることはあきらめました。
やらなかったことは下記の事です。
- 県ごとに大きさを変えること
これもやろうと思えば出来るはずですが、手間のわりにあんまり付加価値高くなさそうだと思いました。だいたいの位置が分かれば十分なんで。
- 0.5 きざみの配置
6角形のタイルを配置するイメージでも作成出来ます（画像リンク注意）。
こっちの方が、正直見栄えはよいと思います。
（もう少し地理的特徴を反映させられそうです）
しかし、デメリットもいくつかあるので、採用しませんでした。
ひとつのデメリットは、二つ目のグラフのような時に、スペースのムダが増えてしまうことです。
このサイトも、ちょっと参考にさせて頂きました。
そして、
最終的に行き着いたのが、こちらになります。
作り方はそんなに難しくありませんが、次回にしようと思います。
データはこちらから。
では、ご自分でトライして楽しんでください。
もっとうまい配置があったら、ぜひ教えてください。
Tableau で Calendar その 4 （全部で４）今日は Videoつきだよん。
ようやく最後です。
見せ方の体裁をもう少し整えます。
Dashboard を使えば、ある程度見栄えのよいものはできますが、ひとつのシートでできる範囲のものに、焦点を絞ります。
最大のポイントは、月の名称をどのように表示させるかです。
まず、Header を全て消すと、下記のようなVIZになります。
修正したいことを先に挙げます。
- 曜日表示を短くする
- 月の区分（切れ目）を分かりやすくする。
- 「何月」の表示を入れる
↑ これだけやろうと思います。
＜曜日表示を短くする＞
曜日の名前は、以下のFormulaで入力されています。
[Weekday Name]
datename('weekday',[Calendar Date])
datenameは、文字列扱いです。
ですので、Mon、Tue などの3文字表記にするために、以下のように変更します。
[Weekday Name]
left(datename('weekday',[Calendar Date]),3)
直りました。
水平方向の表示位置が、真ん中のほうが、いいかな？
Header 位置で右クリックして、Format を選択します。
Header の Alignment を選択します。
水平垂直とも、中央を選んでみます。
ちょっとマシですね。
忘れないうちに、見やすいように、以前に設定した、土日の赤色も追加しておきます。
少し見れるようになってきました。
次です。
＜月の区分（切れ目）を分かりやすくする＞
罫線の話です。土日を赤くしたら、ある程度は分かりますが、やはり月単位の罫線は欲しいです。
表の中の任意の位置で右クリック、Formatを選択します。
左上のアイコンが、Format 対象を示しています。
こいつが罫線です。
列（縦方向）の罫線を変更したいので、Column Devider を変更します。
ここで、Level を分かりやすくするため、Headerを戻してみます。
列方向には、3レベルのHeaderが設定されています。
以下にLevelごとの見え方の違いを示します。
中央のものが月単位に該当します。
もう少し太くしてもよさそうです。
以下が、罫線の出来上がり版です。罫線の色を変更することもできますが、今回は、初期のもので十分です。
さあ、月の名前表示です。
＜「何月」の表示を入れる＞
じつは、これには制約が多いです。Dashboard だとかなり融通は利きますが、先に書いたとおり、手間を最小限にするため、ひとつのWorksheet で実現できることに絞ります。
この場合、Header を除くと、データのないところに表示をさせることが出来ないのです。ちょっと透明性を持たせて、各月の中央あたりに表示できるとベストですが、それは無理です。
↓こういうのは無理 ↓ （ちなみにパワーポイントで重ね合わせてます）
でも、できるだけ近いことをしたいと思います。
データのあるところにしか表示できないので、月の、あるに一点だけ、表示させることにします。
「1日」を選ぶと、こんな感じです。
2段目、左端をターゲットにします。
これでもいいかもです。
真ん中にしてみましょう。
やはり、これが、いちばんマシですかね。
さて、それぞれのFormatです。
- 毎月1日
[Show Month 1]
if [Normal day]=1
then left(datename('month',[Calendar Date]),3) END
- 2段目左端
[Show Month 2]
if ([Weekday ID 3]=1 and [Weekday ID 4] = 2 and [Normal day] <> 8 )
then left(datename('month',[Calendar Date]),3)
elseif [Weekday ID 3]=1 and [Weekday ID 4] =1 and [Normal day] = 1
then left(datename('month',[Calendar Date]),3) END
- 真ん中
[Show Month 3]
if [Weekday ID 3]= 4 and [Weekday ID 4] = 3
then left(datename('month',[Calendar Date]),3) END
それぞれ、日付の属性や、Header の属性をうまく工夫して、所定の位置を特定させていることがご理解頂けると思います。
地味に重要なのは、月にひとつしか表示させないことを徹底することです。2段目左端でだいぶ苦労しました。
最後に、Text の配置 です。
日付の情報 1~31と、上記の月の名前を両方 Label にDrag します。
このとき、実は、全ての日付のLabelにも、月の名前が添付されています。
しかしながら、月に一日以外は「null」になっており、どこにも表示されずにいるだけです。
次に文字のFormatです。
Labelの中身は、かなり詳細設定が可能です。
追加したFieldごとにFontや色や配置が変更可能ですし、独自のText入力も可能です。
ここでは、Field ごとに、サイズ、太字、斜体、配置をそれぞれ変更しました。
Field 同士の相対位置も、平行に配置、垂直に配置と選択できることに注意してください。
表示エリアの制約によって、横に並べた方がいい時も、縦に積んだ方がいい時もあります。改行を入れるだけで、縦積みが可能です。
さらにもうひとつ、TIPS ですが、
表示を入れ替えるとき、以下の違いもあるので覚えておいてください。
ようやくカレンダーが終了しました。
それでは、ご自分でトライして、楽しんでください。
Tableau で Calendar その 3 （全部で３か４）
まだ続きます。
月ごとのカレンダーは出来ましたので、年間カレンダーに移ります。
ここでちょっとだけハードルが上がります。
まず、「月」を縦横に並べるため、縦と横のソートID を付与します。
並び方は、4月が左上、次に右に進んで6月が右上、7月が2段目の左端、 3月が 右下 ということにします。
ですので、縦と横のIDをつけるとすれば、下記の様になりますね。
IF で対応することも出来ますが、あまりかしこくありません。標準で使える計算式を考えます。
月の配置 ： 縦のID
３で割って、切り上げでいけそうです。
[Month ID V 1]
int([Normal month]/3)
こうなりました。一個ずれてます。最初にひとつずらします。
[Month ID V 1]
int ( ([Normal month]-1)/3)
０から始まるのは気持ち悪いので、１ を足しなおします。
[Month ID V 1]
int ( ( [Normal month]-1)/3)+1
OKです。（ちなみに、四半期のIDとしてそのまま使えます）
月の配置 ： 横のID
「1,2,3」「,1,2,3」 のループです。直感として、3で割った余りですね。
Tableau では、「%」がそれにあたります。エクセルの「Mod」ですが、式がちょっと変わります。
[Month ID H 1 ]
[Normal month]%3
いい感じなのですが、「1,2,3」の代わりに「1,2,0」になってます。
ここで「１」を足したらダメですよ。それだと、「2,3,1」になるだけです。
さっきと同じ、1 から始めるのではなく、０から始めます。
[Month ID H 1 ]
( [Normal month]-1)%3
もうちょいです。「1」を足して「0」を回避します。
[Month ID H 1 ]
( ( [Normal month]-1)%3)+1
できました！
さて、この12個の月の枠の中に、日付を入れていきます。
横は、曜日なのでOK だとして、縦が問題です。
一旦、そのまま配置してみます。
（1~53 の週のID を使用します）
横に曜日のID、縦に週のID 、Label に 日付です。
あたりまえですが、ずれちゃいます。ここからはヒント得られないですね。。。
仕方ないので、ロジックだけで考えてみましょう。
- 年間を通じた 週ID は使えません。月ごとに１～最大６を付与する必要があります。
- 週が変わるのは、月曜日です。
何が出来るでしょうか。。。
週の配置：縦のID
まず、前回のdatetruncを使って、直前の月曜日を出します。
[Monday Before]
datetrunc('week',[Calendar Date],'Monday')
この新しい日付を使えば、同じ週であれば同じIDが 得られます。次は、1～６までの算出の仕方を考えます。
すでに、全ての日付を月曜日に固定できているので、月曜日だけを考えます。
つまり、ある月曜日から、その月の最初の月曜日を引けば「7」の倍数で日数が出ます。それを「7」で割ってあげればいい訳です。
でも待って！！
月の最初の月曜日。。。？？？
1/1 が水曜日のときは、直前の月曜日って12/30 になってしまいます。そのまま配置すると、。。。めちゃめちゃになります。
月の最初が月曜日であれば、その月の最初の月曜は「１」、月の最初が火曜日～日曜であれば最初の月曜日は週ID が「２」です。。。。。
IF は使いませんよっ！！ こんなことくらいでは！
うーん。発想の転換が必要です。
ちょっとトイレ行ってきました。
さて、気を取り直して再開。
週の配置：縦のID （再挑戦）
第1週に絶対に存在する曜日。それは月曜日ではありませんでした。
月曜スタートのカレンダーで、どんな組み合わせの月でも、絶対に第1週に存在する曜日は、
そう「日曜日」なのです。
ここからの日付を「7」で割ってあげればよいのです！！。1/1 が月曜日なら、「1」。ずっ～とすすんで1/1が土曜日なら「6」。そして、1/1 が日曜なら「7」！！！
がしかし、また考えます。
第1週の土曜日までは、「月の最初の日曜日」を引き算したらマイナスになってしまいます。 orz
え～っと。。
ようやく気づきました。
必要なのは、直前の日曜ではなく、「直後の日曜日」だったのです。
実際の算式はこうです。
[Next Sunday]
date({fixed [Noramal year],[Normal month]:
Min(datetrunc('week',[Calendar Date]+6,'Sunday'))})
[Weekday ID 4]
int((([Calendar Date]+6-[Next Sunday]))/7)+1
さて、表に配置！
できたっぽい！ 長くなったので、今日はここまで。次回、最後のFormat やりたいと思います。
それでは、ご自分でトライして、楽しんでください。
Tableau で Calendar その2 （全部で３か４）
続きです。
を月曜日始まりにします。
開始曜日を修正する
"weekday" の換算では、Sundayが「1」だったので、月曜日を「1」にするためには。。。
マイナス１にすると、Sundayが「0」になるだけで、変わりません。
こんなのでいけますね。
[Weekday ID 2]
if [Weekday ID] =1 then 7 else [Weekday ID]-1 END
が、なんかズブの素人っぽい ですね。もう少しエレガントに、そして[Calendar Date] から直接計算できる方法を考えます。
[Weekday ID] を計算した後に、If で条件分けするのではなく、日付そのものに手を加えます。
[Weekday ID 2.2]
datepart('weekday',dateadd('day',-1,[Calendar Date]))
[Calendar Date] の一日前の日付の曜日を持ってきてね。としました。
これをカレンダーに持っていって、[Weekday ID] と入れ替えます。
月曜日が先頭に来ました！
が、週の位置がずれてしまいました。
週の番号を修正する
ずれの原因は、
[Week number]
datepart('week',[Calendar Date])
の計算が、日曜日はじまりになっていることが原因です。
さて。。同じことができないか。。
[Week number 2]
datepart('week',dateadd('day',-1,[Calendar Date]))
できました。
基本的にこれでいいんですけど、もうちょっと突っ込みます。
「-1」 って分かりにくいんですよね。金曜日始まりにしたいときは？
「-5」で金曜日です。
これを一目で分かるようにしたいです。
開始曜日を簡単に任意の曜日にする
Date 関連の関数はいろいろあって、そのひとつに、Datetrunc っていうのがあります。
これは、単位期間（年、月、四半期、週、日、時間、分）のスタート時点の値を返す。という使い勝手の良いものです。
上のような感じです。
[Datetrunc Year]
datetrunc('year',[Calendar Date 2])
日付基準の部分（「week」）は、全て小文字である必要があります。
さて、この [datetrunc] 関数ですが、week のときだけ、さらに特別機能があります。
[Datetrunc Week Monday]
datetrunc('week',[Calendar Date 2],'Monday')
のように、後ろに曜日を入れると、直前のその曜日の日付を返してくれるのです。
年とか、月とかは同様の機能はなさそうです。
さて、カレンダーに戻ると、
[Week number 3]
datepart('week',datetrunc('week',[Calendar Date],'Monday'))
[Weekday ID 3]
[Calendar Date]-datetrunc('week',[Calendar Date],'Monday')+1
ということになります。
ID のほうは、日付から直前の月曜日をマイナスすることで日数を計算しています。+1 はおまけで、０ スタートを好むか、1 スタートにするかの差です。
なお、datetrunc は重宝する関数で、例えば月ごとの日数を算出するなどでも使えます。
[Month Days]
datetrunc('month',dateadd('month',1,[Calendar Date 2]))
-
datetrunc('month',[Calendar Date 2])
あるいは、月末の日付特定などです。
[Last Day of Month]
date(datetrunc('month',dateadd('month',1,[Calendar Date 2]))-1)
では、ご自分でトライして楽しんでください。
Tableau で Calendar その１ （全部で３か４）
先日、会計カレンダーの日付コンバージョンについてアップしたのですが、関連して、カレンダービューの作り方について書いておきます。
いきなり年度のカレンダーにいこうかと思ったのですが、想像以上に、STEPが多すぎたので、月度標準カレンダーからはじめます。
最終的には上記のようなVIZになります。
種明かしからすれば、横軸に曜日ごとに番号をつけ、縦軸に週ごとに番号をつけて、表にしてあげれば良いです。
こんな感じに数字が隠れています。
ちなみに、表示は以下のようにShow Headerの ON/OFFで切り替え可能です。
さて、縦軸、横軸にそれぞれ何が入っているかというと、
横軸
[Weekday ID]
datepart('weekday',[Calendar Date])
縦軸
[Week number]
datepart('week',[Calendar Date])
です。
'weekday' は、日曜日から順番に、1~7 で土曜日が７になります。
'week' は、1/1 から、1/2 以降いちばん早い土曜日までが week 1、以降日曜日を迎える度に １ずつ増えていきます。最大53です。
どの週から始まろうが、月でフィルターしているので、少ない番号から順番に並んでくれます。
基本、以上でよいのですが、あと、少しだけビューを工夫します。
曜日の名前をHeaderに表示します。
曜日の名前は、下記の式です。
[Weekday Name]
datename('weekday',[Calendar Date])
これで、"Sunday" ～"Saturday" に変換されます。日本語版を使っていないので、「日曜日、土曜日」等の日本語標記がどうなるかは、残念ながら検証してません。
（どなたか教えてください。）
それともうひとつ、土曜日、日曜日を赤色で、他の曜日を黒色で表示します。
[Weekday Color]
if [Weekday ID]=1 or [Weekday ID]=7
then "Red" else "Black" END
色は、Red、Blackと表記するだけでは自動では変更されないので、Manualで変更します。
同様にして、Redを赤にします。
できました。
Headerを非表示にすると、下記の通りです。
今日はここまでで終わりたいと思います。
カレンダーやり出すと、手がかかり出します。
しばらく続ける予定です。次回は、曜日の始まりを月曜日にします。
それでは、ご自分でトライして楽しんでください。
製品Aと製品B 両方ともお買い上げのお客さま～
さて、Kaori さんの 投稿から
Twitterでいろいろ見ていると、Tableauに関してのAkiyah さんのブログを発見しました。
標題の件は、過去に何度か質問をされた方もある内容で、
『２つの商品を両方とも購入した顧客を抽出する』
という課題です。
Akiyah さんはLODを用いて４つのフィールドを作成し、VIZを作成されています。
ただ、ブログ内にもある通り、
計算フィールドを4つも作らないといけないところがつらいところです。 もっといい方法があるよ、という方、教えて下さい。
ということを書かれています。
そこで、これにお答えして、ちょっと別の方法をご紹介したいと思います。
LODを使用するのは同じですが、4つのMeasure Fieldsを作成する代わりに、一つのDimensionを作成して、カテゴリ分けしてしまいます。
作成する Calculated Fieldは、ちょっとややこしいですが、全体で見れば、より使いやすいと思います。
使いやすさの一つの例も紹介します。
Calculated Field を作成する
LODを用いて、Field内で条件分けを複数作成します。
[Customer Category]
if
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 1] then [Sales]end)}>0
and
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 2] then [Sales]end)}>0
then
"1-Purchased both "+[Param 1]+" and "+[Param 2]
elseif
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 1] then [Sales]end)}
=
{fixed[Customer Name],[Order month] :sum([Sales])}
then "2-Purchased "+[Param 1]elseif
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 2] then [Sales]end)}
=
{fixed[Customer Name],[Order month] :sum([Sales])}
then "3-Purchased "+[Param 2]ELSE
"4-No purchase on "+[Param 1]+" nor "+[Param 2]
END
ちょっと長い？ そうですね。ちょと解説を付けます
[Customer Category]
if
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 1] then [Sales]end)}>0 // 該当月、該当顧客のうち、カテゴリがParam 1 に合致する売上げを合計して、0 より大きいかどうかを判定and
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 2] then [Sales]end)}>0 // 該当月、該当顧客のうち、カテゴリがParam 2 に合致する売上げを合計して、0 より大きいかどうかを判定
then
"1-Purchased both "+[Param 1]+" and "+[Param 2] // 両方に合致すれば値を返す。（＝Param 1, Param 2 両方に売上げがある場合のみ）
elseif
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 1] then [Sales]end)} // 該当月、該当顧客のうち、カテゴリがParam 1に合致する売上げを合計
=
{fixed[Customer Name],[Order month] :sum([Sales])} // 該当月、該当顧客の売上げを合計
then "2-Purchased "+[Param 1] // 両者が合致すれば値を返す。（両方が同じであれば、結果的に Param 1 のみに売上げがある場合となります）elseif
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 2] then [Sales]end)} // 該当月、該当顧客のうち、カテゴリがParam ２に合致する売上げを合計
=
{fixed[Customer Name],[Order month] :sum([Sales])} // 該当月、該当顧客の売上げを合計
then "3-Purchased "+[Param 2] // 両者が合致すれば値を返す。（両方が同じであれば、結果的に Param ２ のみに売上げがある場合となります）ELSE
"4-No purchase on "+[Param 1]+" nor "+[Param 2] // 上記以外の場合
END
できましたか。
テキストに、1~4 の番号を追加したのは、強制的にその順序でソートするためです。必須ではありません。ちょっとしたノウハウです。
これで、このFieldをColor Shelf に追加すれば、色分けは完成です。ついでにLabel Shelfにも追加してLegend がなくても分かるようにしました。
このソリューションのもう一つのメリットは、表示が「Param-1」 とかだと、どのカテゴリか分かりにくかったのが、選択されたカテゴリ名を表示することで一目で分かるようになることです。
でもまだ、なんとなく物足りません。
というのは、両方買った顧客のことを強調したいので、それを工夫したいのです。
そこで、さらに一計を案じます。
強調１-Colorを変える
1- のカテゴリを強調する＝他のカテゴリを目立たないようにします。
Edit Colorsで、薄めの配色を選択します。
次に、強調したい1- のカテゴリのカラーのところでW-Click します。
Paletteが開くので、濃い色を選択します。
センスはともかく、ちょっと強調できました。
強調-２ Label サイズを変える
さらに、もうひとつ欲張ります。
1- のカテゴリだけ、Labelを大きくしたいのです。
。。。
これは標準では出来ないので、ちょっとしたトリックを使います。
さっきの色と一緒で、「1-」 と それ以外 で違うLabelを使います。そのために、Labelのためだけに、二つのFieldを追加します。
実行するのはいたって簡単。
[HighLight]
if [Customer Category] = "1-Purchased both "+[Param 1]+" and "+[Param 2]
then [Customer Category] end
[Non-HighLight]
if [Customer Category] <> "1-Purchased both "+[Param 1]+" and "+[Param 2]
then [Customer Category] end
上記の２つのFieldです。
ここから、２つともLabel Shelfに追加します。
Label ShelfをClickしたあと、詳細設定を開きます。
詳細設定のダイアログの中で、改行を解除し、Highlight のみ選択し、サイズを変更し、と太字にします。
Labelは、複数設定したとき、別々のフォーマットが使えるのです。
なお、Fieldの計算式で、Else 以下を省くことで、条件に合わない場合は[null] 扱いとなっています。[null] は、#NA などの表示がされず、自動的に空白（スペースにもならない）扱いされるので、2つのFieldの条件が同時に成り立たない限り、重複して表示されることはありません。
最終的に、こんな感じの出来上がりです。
では、ご自分で試して楽しんで下さい。
20日締めとか4月始まりとか。。。会計月度計算 その2
さて、会計カレンダーその２です。
前回は、日付から「月度」を算出して、四半期、年と計算して行ったのですが、
年度と月度が会わない月（12月度を特別扱い）とか日付（12/21~12/31 を特別扱い）とか、ちょっと面倒な面がありました。
劇的に改善できるわけではないのですが、少しでもエレガントにしたいと思います。
加えて、カレンダー関連の数字を扱うときに一般的なやり方も含むので、他のケースでも応用可能だと思います。
それぞれの日付から直接、月、四半期、年を計算するのはその都度検証が必要で面倒だったので、今回は、あえて一段階ステップを増やして、「日付ごとに会計カレンダー用の違う日付に変更」してしまいます。
といっても、会計カレンダーの場合、「何年何月何日」を設定しようと思ったとき、「何年何月」までは問題ないのですが、「何日」で問題が生じます。
なぜでしょう？
実は例えば、20日締めで翌月になる場合、2月度は 1/21 ～2/20 となり、日数は31日となります。1/21～1/31 = 11日 、2/1～2/20 = 20日 で31日です。
つまり、2/30、2/31 という通常カレンダーでは現れない日にちが必要になるのです。
これでは、日付を 1対１でコンバートする訳にはいきません。
ですのでここでは、日付ごとにその日付の属する会見月度の最終日を割り振ることにします。
毎月20日を越えると（21日から）、通常の月度の翌月になります。
月度を計算してから「１」を足すと、13月が生じてしまうため、日付計算の計算式の中にこのロジックを取り込んでしまいます。
そうすれば、１２月の翌月は自動的に１月になるし、自動的に翌年度になる訳です。
実際の計算式は、複数の日付に関する関数を使います。
[Fiscal calc date end 2]
date(
str(year(dateadd('month',iif(day([Calendar Date])>20,1,0),[Calendar Date])))
+","+
str(MONTH(dateadd('month',iif(day([Calendar Date])>20,1,0),[Calendar Date])))
+",20")
date() 関数は、()の中身が文字列（str）のとき、特定のFormatを満たしているとき、それを日付に変更してくれます。
目指すのは、"yyyy,mm,dd"のFormatなのですが、yyyy、mm、dd ともちょっと工夫しています。
yaer(日付のFormat)
- 年を４桁の数字で返します。
dateadd('カレンダーの単位',数字,日付のFormat)
- カッコ内の日付から、数字分の単位（月、年、週など）をずらした日付を返します。
day(日付のFormat)
- カッコ内の日付から「日」を返します。
str(数字)
- 数字を文字列で返します。
iif(条件,x,y)
- 条件に合致すれば xを、合致しなければ y を返します。
全部合わせて無理やり翻訳すれば、
『[Calendar date] から、20日/21日を境にして、一番近い未来の20日を日付Formatで返しなさい』
ということになるかと思います。
長々と書きましたが、とにかくこれで、日付ごとに一番近い未来の20日が算出できました。
月度は、この新しい日付自身の月度を
[Fiscal month 2]
month([Fiscal calc date end 2])
で計算すれば良いわけです。20日の条件分けはすでに出来てるので、そのまま月度が取得できます。
年度、四半期に関しては、そのままだと標準カレンダーに従ってしまうので、[Fiscal month 2]の月自身を3ヶ月ずらします。
ここでもdateaddを使うわけですね。
[Fiscal Year 2]
year(dateadd('month',-3,[Fiscal calc date end 2]))
[Fiscal Quarter 2]
datepart('quarter',(dateadd('month',-3,[Fiscal calc date end 2])))
ちょっとだけ簡単になってませんか？なってない。。。！？
最初の日付のコンバートが余計にややこしいですかね？
そうかも知れません。
ただ、ポイントは、アプローチのやり方が複数あるってことです。
1.『四半期や年度や月度を一旦計算した後で、特別扱いの条件を加える』
2.『四半期や年度や月度を計算する、もとの日付自体を変える』
の２つがあります。
それぞれ、その１（前回）．その２（今回） に 示したことになります。
どっちのアプローチを使うべきかは、簡単さ、間違いの生じにくさ、事後の解析のし易さ でケースバイケースの判断になると思います。
それでは、ご自分で試して楽しんでください。
20日締めとか4月始まりとか。。。会計月度計算 その１
今日は趣向を変えて、カレンダーに関するトピックです。
通常の日付から、20日締めの会計月度や、4月始まりの会計年度を計算します。
If 構文を使えば、できるのです（その１）が、そこにいくつか工夫を加えてちょっと簡単にしてみたいと思います（その２）。
まずは、分かりやすいように、「日付」、「月」、「年」を日付データから予め抽出します。
[Normal day]
day([Calendar Date])
[Normal month]
MONTH([Calendar Date])
[Noramal year]
YEAR([Calendar Date])
会計「月」の計算は、
[Fiscal month 1]
if [Normal day]>20 then [Normal month] +1 else [Normal month] END
としたいところですが、これだと、答えが「13」を含んでしまいます。
ですので、以下のような回避方法を取ります。
[Fiscal month 1.1]
if [Normal day]>20 and [Normal month] <12 then [Normal month] +1
elseif [Normal day]>20 and [Normal month] =12 then 1
else [Normal month] END
13がでるのは、12の時だけなので、12だけ特別扱いしています。
会計「月」が出せれば、四半期は簡単です。
[Fiscal Quarter 1]
if [Fiscal month 1.2] <=3 then 4
elseif [Fiscal month 1.2] <=6 then 1
elseif [Fiscal month 1.2] <=9 then 2
elseif [Fiscal month 1.2] <=12 then 3
END
年は少しややこしいです。
[Fiscal Year 1]
if [Fiscal month 1.2]<=3 then [Noramal year] -1 else [Noramal year] END
これだと、12/21～12/31 までがおかしくなってしまうのです。
なので、
[Fiscal Year 1.1]
if [Fiscal month 1.2]=1 and [Normal month]=12 then [Noramal year]
elseif [Fiscal month 1.2]<=3 then [Noramal year] -1 else [Noramal year] END
一応、これでできました。
月や年の出し方がちょっとエレガントさとは、かけ離れています。。。
ですので、次回、もう少し別のアプローチを考えます。
それでは、ご自分で試して、楽しんでください。
棒グラフに2つ目のカテゴリーで色づけする
さて、今日は積み重ね棒グラフで、2つのカテゴリで色を付けてみます。
完成形を示したほうが早いと思います。以下のようなグラフを作成したいという意味です。
実は、知られている簡単な方法はあって、２つのDimensionをColor Shelfに持っていくというものです。
できました。
これはこれで便利ですし簡単です。
しかしながら、カラー Legendが ９種類になっていて、見にくいのと、色を変えたいときにすごーく面倒くさいのが難点です。加えて、（これがいちばん難ですが）Product Categoryの色をカスタマイズしているときは、カスタムカラーが無視されるという不具合もあります。
ですので、これとは 別の解法を示します。
スタートは、下記のシンプルなビューです。
あえて、Product Categoryの色をカスタマイズしています。
まず、"Sales" Fieldをもうひとつ、2ndary Axisに設定します。
Synclonizeも忘れずに。
棒グラフが２つになりました。
２軸目のグラフのColor Shelfに、"Ship Mode"を追加します。
こうなります。
元のカラーが、2軸目のカラーで上書きされました。
2軸目のカラーの"Edit Colors"を選択します。
Gray 5 あたりがよろしかろうと思います。
このままだと、濃淡が順序よくなっていないので、Delivery Truck のところをダブルクリックして、色を変更します。
さらに、2軸目の Color を"Edit Color"で、Transparencyを下げます。
25%で下のようになります。適当に選んでください。
2軸目の 表示を隠して、
Label を追加します。
これで、いちおう出来上がりです。
これだと、カスタマイズした"Product Category" の色調はそのままで、濃淡で"Ship Mode"の違いを表すことができます。
それでは、自分でトライして、楽しんでください。
