Amazon QuickSight に比較および累積の日付/時刻計算を追加する

この記事は “Add comparative and cumulative date/time calculations in Amazon QuickSight” を翻訳したものです。

Amazon QuickSight は最近、比較 (前年比など) および累積 (年初来など) の期間関数をネイティブにサポートするようになり、これらの計算をビジネスレポート、傾向分析、時系列分析に簡単に導入することができるようになりました。これにより、QuickSight の作成者は、日時対応の比較をするために複雑な日付オフセットを用いたりすることなく、高度な計算を実装できます。

この記事では、新しい期間関数とその機能を紹介し、いくつかの典型的なユースケースをお見せします。また、期間関数の利用の幅を広げるいくつかのシナリオについても説明します。これらはより高度なことにチャレンジする状況で役に立ちます。

新しい期間関数

ユースケースをお見せする前に、一連の新しい期間関数を確認し、どのような関数がサポートされているのかを見てみましょう。期間関数は、比較 (period over period) 関数と累積 (period to date) 関数の 2 つの主要なグループに分けることができます。

比較 (period over period) 関数

period over period 関数を使用すると、年、四半期、月など、異なる期間のメジャーを比較できます。たとえば、前年比の売上の増加や、前週比の収益変化率を計算できます。

典型的な period over period 関数は
periodOverPeriodDifference(measure, date, period, offset)
という構文を持ち、2 つのオプショナルな引数periodとoffsetがあります。

関数にperiod引数を指定することで、計算の期間の粒度を定義できます。YEARの粒度は前年比の計算を、QUARTERは前四半期比の計算を意味し、以下同様となります。period 引数を空白のままにすると、ビジュアルの表示のために (フィールドウェルで) 選択された期間の粒度に従い計算が変化します。

また、offset引数を使用することで、比較を計算する期間同士をいくつ離すかを指定することができます。たとえば、period が QUARTER で offset が2の場合は、2 つ前の四半期と比較することを意味します。

periodとoffsetの両方を指定するか、両方を空にしておく必要があることに注意してください。どちらか 1 つだけを指定することはできません。

次の表は、利用可能な 3 つの period over period 関数をまとめたものです。

累積 (period to date) 関数

period to date 関数を使用して、特定の期間内のメトリクスを計算できます。period to date 関数には主に 2 つのタイプがあります。

• “OverTime” 関数。これはテーブル計算で、ビジュアルの行ごとに結果を返します。
  たとえば、periodToDateCountOverTime をWEEKの粒度とともに使用することで、週初来の一連の新規顧客数を計算し、顧客エンゲージメントの変動を追跡できます。
• 集計関数。これはある時点から現在までの期間の時間範囲に対して集計された値を返します。
  たとえば、YEARという粒度を持つ periodToDateSum は、その年の初めから、式で指定されたendDateまでのメトリクスの合計である値一つを返します。endDateの指定が無い場合、デフォルトのendDateはnow()が返す値、つまりユーザーがダッシュボードをロードした時点の時刻となります。

次の表は、period to date 関数をまとめたものです。

ユースケース 1: period over period 関数を使用して、固定および動的な粒度で売上の伸びを分析する

period over period 関数が一般的なビジネスおよび財務のユースケースをどのようにサポートできるかを見ていきましょう。次の例では、periodOverPeriodDifference を使用して YoY (前年比) の売上増加を計算しています。SegmentとRegionがビジュアル内にあるため、YoY 売上高 (YoYSales) はセグメントと地域ごとに計算されます。

YoYSalesというメジャーは次の式で定義します。
YoYSales=periodOverPeriodDifference(sum(Sales),{Order Date},YEAR,1)

最初の引数sum(Sales)は、このメジャーに基づいて計算を行うよう関数に指示します。2 番目の引数Order Dateは、Yearの情報の抽出元となる日付/時刻カラムを指定します。3 番目の引数YEARは、この計算の粒度を指定します。このオプショナルな引数を指定すると、ビジュアルに表示されるOrder Dateが (フィールドウェル内で) どのような選択になっているかに関係なく、このメジャーは常に (QoQ や MoM ではなく) YoY を返します。4 番目の引数1は、比較のオフセットを指定します。この例では、各注文日の売上を前年の同じ日付と比較したいという意味です。2018 年の注文日のメジャーについては、比較する対象の期間が存在しないため空となります。

期間関数は合計と小計についても動作します。カラムの合計をビジュアルに追加すると、Salesの合計とYoYSalesの合計を地域ごとに確認できます。

計算の粒度を指定するオプショナルな引数periodを指定せず、次の例に示すように式をPoPSales=periodOverPeriodDifference(sum(Sales),{Order Date})に変更すると、ビジュアルに表示されるOrder Dateの粒度によって計算の期間が決定します。次の例では、Order Date は四半期レベルで表示されるように (フィールドウェルで) 選択しているため、PopSalesは QoQ (前四半期比) での売上成長率を動的に計算します。Order Dateを月レベルに変更すると、メジャーが更新され、MoM (前月比) が計算されます。PopSalesの場合、2018 年第 1 四半期のみが空になります。これは、この四半期のみ比較すべき直前の四半期がないためです。

前の例のYOYSalesをこのビジュアルに追加すると、YoY 売上高の伸びが四半期レベルで計算されます (2019 年第 1 四半期の売上高と 2018 年第 1 四半期の売上を比較します)。これは、period over period 関数の期間粒度を固定にした場合と動的にした場合との違いを示しています。

period over period 関数では、正の変化 (増加) と負の変化 (減少) を明確化できます。したがって、ビジュアルに条件付き書式を追加すると、各期間の財務パフォーマンスが非常にわかりやすくなります (緑は良好、赤は不良)。

同様に、periodOverPeriodPercentDifference を使用して、時間の経過に伴う相対的な売上増加を計算できます。ビジュアルにディメンションを追加して、各事業セグメントの四半期ごとの売上高の変化の内訳や、総売上増加への寄与を分析するなど、ビジネスインサイトをさらに掘り下げることができます。これには以下の式を使います。

PoPSales%=periodOverPeriodPercentDifference(sum(Sales),{Order Date})

ユースケース 2: period to date 関数を使用して、テーブル計算と集計で YTD 売上を追跡する

period over period 関数と同様に、period to date 関数は、年初来累計 (Year-To-Date / YTD) または四半期累計 (Quarter-To-Date / QTD) のメトリクスをすばやく簡単に計算する方法を提供します。次の例では、YTDSumoverSales=periodToDateSumOverTime(sum(Sales),{Order Date},YEAR)およびYTDtotalcountSales=periodToDateSumOverTime(count(Sales),{Order Date},YEAR)の式を使用して、YTD 売上高とYTD トランザクション数を計算します。

period over period 関数とは異なり、period to date 関数の 3 番目の引数periodはオプショナルではありません。したがって、計算の粒度は常に固定されます。この例では、粒度を YEAR と定義すると、このメジャーは QTD または MTD ではなく、常に YTD を計算します。Order Dateは月レベルで表示されているため、この計算では各月の YTD 売上高が出力され、翌年の 1 月に再び計算が開始されます。結果テーブルに示すように、2018 年 1 月のYTDSumoverSalesは 2018 年 1 月の月間売上高であり、2018 年 2 月のYTDSumoverSalesは 2018 年 1 月の月間売上高に 2018 年 2 月の売上高を加えたものです。また、2019 年 1 月のYTDSumoverSalesは 2019 年 1 月の月間売上高に戻ります。

計算を折れ線グラフに入力し、分析にディメンションを追加することで、詳細をさらに掘り下げることができます。次の例は、過去 4 年間における各地域の YTD での週次の売上成長傾向を示しており、2021 年における AMER と EMEA の間の興味深い販売競争を明らかにしています。

テーブル計算に加え、集計タイプの期間関数は 、YTD 指標をリアルタイムで評価する KPI チャートを作成する必要がある場合にとりわけ役に立ちます。次の例では、集計タイプの period to date 関数を使用して 2 つの KPI チャートを作成し、YTD の総売上高と YTD の合計トランザクション数を追跡しています。2021 年 12 月 26 日の場合、ダッシュボード表示時点の結果は、テーブル内の 2021 年 12 月 26 日に対応する計算結果に一致します。次の表は、式をまとめたものです。

高度なユースケース 1: 期間関数の日付/時刻認識

期間関数は、定義や読み取りが簡単なだけでなく、日付/時刻の認識に対応しています。つまり、関数は固定行数ではなく日付/時刻ベースのオフセットに基づいて計算されます。これにより、これまで対処できなかった2つの大きな問題を解決できます。

期間関数は変動する期間を処理できる

MoM での売上増加を日次で計算したい場合、各月の日数が (1 月は 31 日、2 月は 28 日または 29 日と) 異なるため、各月に固定オフセットを使用することはできません。

期間関数の場合、固定オフセットではなくカレンダー日付に基づいて計算されます。次の例では、MoMsalesDiff=periodOverPeriodDifference(sum(Sales),{Order Date},MONTH,1)
という式を使用します。日次の MoM 増加は、その月の何日目であるかに基づいて正しく計算されます。月の最初の日の売上高は前月の最初の日と比較され、他のすべての日にも同じことが適用されます。(ビジュアルはデモンストレーションのために複製されています)。

期間関数はスパース (欠落) データポイントを処理できる

すべてのデータセットで完全な日付セットが保証されるわけではありません。前の例では、2018 年 1 月 1 日の売上データが欠落しています。固定オフセットに基づく回避策を使用すると、2018 年 2 月 1 日を 2018 年 1 月 1 日ではなく別の日付と比較してしまうため問題が発生する可能性があります。期間関数では、常に日付/時刻オフセットでメジャーが比較されるため、必要な日付のみが比較されます。前の例では、2018 年 1 月 1 日のデータが欠落しているため、2018 年 2 月 1 日のMoMsalesDiffには空が表示されています。

高度なユースケース 2: 期間関数を他の計算にネストする

period over period 関数とperiod to date 関数を使用して計算フィールドを作成できるようになったので、これらの関数を他の計算にネストして、より高度な分析を推進できます。

たとえば、前週比の売上高伸び率について、各年で上位 10 週がどの週かを知りたい場合があります。これを行うには、最初にWoWSalesを計算します。

WoWSales=periodOverPeriodDifference(sum(Sales), {Order Date}, WEEK, 1)

次にdenseRankウィンドウ関数にネストします。

RankWoWSales=denseRank([WoWSales DESC],[{YEAR}])

これは、計算フィールドではなく、ビジュアル上の計算処理を使って実装される固定行ベースの回避策では不可能です。次のビジュアルでは、RankWoWSalesにシンプルなフィルターをかけることで、年間で最も売上が伸びた上位 10 週を取得しています。

さらに期間関数を他の期間関数にネストして、興味深いインサイトを生成することもできます。たとえば、月間の YTD トランザクション数に基づいて、月次で YoY の成長を計算できます。次の式は、YTD 計算フィールドを YoY 計算フィールド内にネストできることを示す例です。

YTDtotalcountSales=periodToDateSumOverTime(count(Sales),{Order Date},YEAR)
YoYYTDSalesCount=periodOverPeriodDifference(YTDtotalcountSales,{Order Date},YEAR,1)

高度なユースケース 3: 部分的な期間の比較

最後に、3 つ目の高度なユースケースである部分的な期間の比較について説明します。例えば今日が 2021 年 11 月 15 日 (2021 年の最後の四半期の 46 日目) だとし、今四半期の実績と過去の四半期を比較したいとします。ただし、四半期全体ではなく各四半期の最初の 46 日間のみを使用することとします。これには、sumIf()ウィンドウ関数をネストしたperiodOverPeriodDifferenceを使った計算フィールドが必要です。

次の例は、ネストされた計算フィールドを使用してこのユースケースに対処する方法を示しています。

QuarterToDate=periodToDateSumOverTime(sum(Sales), {Order Date}, QUARTER)
PartialQTDSales=sumIf(Sales, dateDiff(truncDate("Q", {Order Date}), {Order Date}, "HH") <= dateDiff(truncDate("Q", now()), now(), "HH"))
PartialQoQQTDSales=periodOverPeriodDifference(sumif(Sales, {Order Date} <= addDateTime(dateDiff(truncDate("Q", now()), now(), "HH"), "HH", truncDate("Q", {Order Date}))), {Order Date}, QUARTER, 1)

PartialQTDSalesは、今四半期の初めから現在の日付までの時間数を計算し、sumIf()を使用して各四半期のその期間の総売上高を計算します。次にpartialQoQQTDSalesは periodOverPeriodDifference 関数にPartialQTDSalesをネストして、部分的な QoQ 差を求めます。部分的な期間に基づくこのような比較は、新しい日付/時刻対応の期間関数なしでは実現できません。

まとめ

このブログでは、すばやく強力な日付/時刻ベースの計算を可能にする QuickSight の新しい期間関数を紹介しました。比較と累積の期間関数 (つまり、period over period と period to date) を確認し、2 つの主要なユースケース (固定粒度と動的粒度、テーブル計算と集計) について説明し、そして 3 つの高度なユースケースへと利用を拡大しました。期間関数は、QuickSightがサポートされているすべてのリージョンで一般利用可能になっています。

これらの計算をビジネスニーズにどのように適用したかについて、皆さんのストーリーやフィードバックをお待ちしています。

翻訳はソリューションアーキテクトの 吉成 貴裕 が担当しました。原文はこちらです。

著者について

Emily Zhu は、AWS のクラウドネイティブかつフルマネージド型 SaaS BI サービスである Amazon QuickSight のシニアプロダクトマネージャーです。QuickSight のコア分析および計算機能の開発を主導しています。AWS に入社する前は、Amazon Prime Air のドローンデリバリープログラムおよびボーイング社でシニアストラテジストとして数年間働いていました。エミリーは、クラウドベースの BI ソリューションの可能性に情熱を注いでおり、お客様がデータドリブンな戦略立案を進めるお手伝いをすることを楽しみにしています。

Rajkumar Haridoss は、 AWS QuickSight のシニアソフトウェア開発エンジニアです。Query Generation チームのリードエンジニアであり、QuickSight のバックエンド計算、クエリプランニング、クエリ生成レイヤーを担当しています。仕事以外では、家族や4歳の子供と充実した時間を過ごすのが好きです。