折れ線グラフで実績を実線で、見込みを破線で表示する方法

折れ線グラフを作成する際、特定のポイントで実線から破線に切り替えて表示したいという
ご要望はありませんか？
1つの数式の中で実線と破線を切り替える機能は残念ながらありません。
しかし、数式を2つに分ければ実線と破線の表示が可能です！
今回は、実線から破線に切り替えて折れ線を表示する裏技をご紹介します。

※本記事は「2022年07月時点のSaaS」で作成しています。

データ例

各「年月」の「金額」に予算と実績の「フラグ」を持つ、以下のようなデータを例に扱います。
見込は全年月に紐づいており、実績がある年月には実績のフラグもあります。
202207までは実績があるため見込と実績両方の金額を、202208以降はまだ実績が無いため
見込の金額のデータのみあります。

完成イメージ
実績がある202207までは実績の金額を実線で表示し、実績がない202208以降は見込の金額を破線で
表示するグラフを作成します。

実績を表示する折れ線と見込を表示する折れ線を別に作成することで、実績を実線で、見込を破線で
表示できます。

＜手順＞
1) シートを編集モードで開き、シート上に折れ線チャートを配置します。
2)[軸を追加] をクリックし、「年月」を選択します。

3) 画面右のプロパティ パネルで[データ]タブを開き、[メジャー]-[追加]をクリックし、
[fx](数式エディタを開く)をクリックします。

4) 以下のような数式を設定し、[適用]をクリックします。
If(Sum({<フラグ={'実績'}>}金額)>0,Sum({<フラグ={'実績'}>}金額))

5) 手順4で追加したメジャーの[ラベル]に「実績」と入力し、[数値書式]を任意に設定します。

6) メジャー「実績」のメニューを閉じ、[追加]をクリックし[fx](数式エディタを開く)をクリックします。

7) 以下のような数式を設定し、[適用]をクリックします。
If(Sum({<フラグ={'実績'}>}金額)<=0,Sum({<フラグ={'見込'}>}金額),
If(年月=Max(Total {<フラグ={'実績'}>} 年月),Sum({<フラグ={'実績'}>}金額)))

8) 手順7で追加したメジャーの[ラベル]に「見込」と入力し、[数値書式]を任意に設定します。

9) [スタイル]-[プレゼンテーション]タブを開き、[スタイル指定]を選択します。

10)[追加]をクリックし、[実績]を選択します。

11) 追加された「実績」のメニューを展開し、[線の種類]で[実線]を選択します。

12) [追加]をクリックし[見込]を選択します。

13) 追加された「見込」のメニューを展開し、[線の種類]で[破線]を選択します。

14)グラフに数値を表示する場合は、[スタイル]-[プレゼンテーション]タブを開き、以下の設定をします。
・[データポイントを表示します]にチェックを入れる
・[値ラベル]を[自動]に設定する

15) 折れ線の色を一色にする場合は、[スタイル]-[色と凡例]タブを開き、[色]の[自動]をオフにし、[単色]を
設定します。

16) 目盛を0から開始する場合は、[スタイル]-[Y軸]タブのメニューを開き、[範囲]の[自動]をオフに設定後、
[最小値]を0に設定します。

実績と見込の数式でそれぞれIf関数を使用し、実績を集計する線と見込を集計する線を分けています。
それぞれの数式について解説します。
※If 関数の条件が複雑な場合やデータ量が多い場合はパフォーマンスに影響する可能性があります。
念のため数式設定がパフォーマンスに影響しないかを ご検証の上、実装をご検討いただくことを推奨します。

実績の数式
If(Sum({<フラグ={'実績'}>}金額)>0,Sum({<フラグ={'実績'}>}金額))

If 関数で、「フラグ」が実績である金額の合計が0より大きい(実績がある)場合に実績の金額を
集計するよう指定しています。
チャートの軸である年月ごとに条件が判定されるため、実績を持つ202207までは実績の折れ線が表示され、
実績を持たない202208以降は線が表示されません。

なお、実績の金額は set分析を用いて以下のように集計しています。
Sum({<フラグ={'実績'}>}金額)

set分析については以下の記事や動画でご紹介しておりますので、詳しく知りたい方はぜひご覧ください！

破線の数式にも202207の実績を表示させることで、2つの線がつながります。

また、「実績を持つ年月の中で最大の年月（202207）」という条件は以下の数式で表しています。
年月=Max(Total {<フラグ={'実績'}>}年月)

Max 関数で年月の最大値を取得する際、Total 修飾子を使用しています。
これにより、軸に関わらず実績を持つ全年月の中での最大値を取得でき、各年月が最大の年月と同じかを
比較できます。
Total 修飾子については以下の記事でご紹介していますので、ぜひご覧ください！
データ分析の基本～「集計関数の使い方」～

今回は折れ線を実線と破線を組み合わせて表示する裏技をご紹介しました。
1つの数式の中で実線と破線を切り替える機能は残念ながらありませんが、実線と破線を組み合わせて
表示したい！という場合は本記事をご参考に設定してみてください！