＜質問＞
Quick Sightの計算フィールドや機能のみで、この区分A・Bの不整合を解決する方法はありますか？
特に、当年データと前年データが別の区分に分かれていても、正しく前年の値を表示させるための具体的な数式があれば、ご教示いただけると幸いです。
＜背景＞
ーーーーーーーーーーーーーーーーー
当年と前年の売り上げを比較するレポートを作成しています。
しかし、一部の店舗で当年と前年で「A・B」の区分が変更された場合、前年数が正しく集計されず、値がずれてしまいます。
現在の状況
データ: 1つのデータセットに、当年と前年のデータが入っています。
問題:A・Bの区分が変更された店舗について、前年数が正しく表示されません。
例:
区分Aだった店舗がある月の中でBに変更。（フラグを０か１で立てており判定しています。この場合０と１両方のレコードが作成されそれぞれで集計されるようになっています）
この場合、当年レコードの前年列のBに前年レコードの当年値であるAの数を表示させたいが、値が0やNULLになってしまう。
これまでに試したこと
・periodOverPeriodLastValue関数→
Aと非Bの区分がずれると、正しく集計されません。
・sumOver関数→
sumOverとaddDateTimeを組み合わせて前年を計算しようとしましたが、月フィールドのデータ形式に問題があり数式が機能しませんでした。
・日付フィールドの変換→
月フィールドをparsedateで日付型に変換しましたが、データに無効な値が含まれていたため変換できませんでした。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーー
お忙しいところ恐縮ですが、ご回答いただけますと幸いです。
よろしくお願いいたします。
@chiemi.shimoda さん、ご質問ありがとうございます。
おおよその課題は頂いたご説明で理解できたつもりではあるのですが、念のため認識齟齬が無いよう、ダミーデータで構いませんので実際のデータモデルに即したデータを例示いただけますでしょうか。
ご返信ありがとうございます。
以下が想定しているデータ状況です。
実現したいデータ
実際のデータ
2025/8の前年の区分Aが空欄となります。
小計では2024/8当年と2025/8は一致していますが区分別で見たとき一致しません。
区分で分けて表示できない場合、せめて小計と区分内合計は一致したいです。
@chiemi.shimoda さん、サンプルデータのご共有ありがとうございます。
頂いたサンプルはいずれもピボットテーブルにした際のイメージのようですが、元のデータソースは以下のようなデータと想像しています。認識相違ございませんでしょうか？
上記の理解でよろしい場合、まずは以下のようにsumOver関数で各月の店舗毎の売上げを「店舗別集計」として集計し、その値をperiodOverPeriodLastValue関数で「前年度売上」として追加してあげると、最後に提示いただいたようなピボットテーブルにすることが可能です。
-
店舗別集計
sumOver(当年度売上, [日付, 店舗], PRE_FILTER)
-
前年度売上
periodOverPeriodLastValue(sum(店舗別集計), 日付, 'YEAR', 1)
-
テーブル
-
最終的なピボットテーブル
なお、2025/8月には区分Bに該当するレコードが存在しないため、前年度の表示先は区分Aの行になる点はご了承ください。最初のご要望のように前年度数値を区分A: 100、区分B: 200というように分けて集計することも同じ理由でできません。（どうしても区分を分けて表示したい場合には区分Bに該当するレコードを作成しておくようデータソース側での対応が必要です）
以上、よろしくお願いします。
1 Like
ありがとうございます。
データの構成はご認識の通りです。
またご教示いただいた計算フィールドを実行したところ、店舗別集計はうまくいきましたが前年度売上フィールドがすべてNULLになってしました。
テーブルの粒度に応じて以下のように[地域,店舗,区分]と使用フィールドを入れてみましたがNULLとなります。
関数の設定について不備があればご教示いただけますでしょうか
＜前年度売上フィールド＞
periodOverPeriodLastValue(sum({売上}), 年月, ‘YEAR’, 1, [地域,店舗,区分])
また、カスタムSQLを利用し、今回の要望を満たせる方法があるでしょうか。
データソースで以下画像のように前年列を追加したデータセットをカスタムSQLで作成するイメージです。
periodOverPeriodLastValue(sum({売上}), 年月, ‘YEAR’, 1, [地域,店舗,区分])
periodOverPeriodLastValue関数は引数としてパーティションを指定できないため、上記の計算フィールドは本来エラーになるかと思います。ご確認いただけますでしょうか。
カスタムSQLを使いデータセットに前年列を作成する方法としてはLAGウィンドウ関数を使う方法が考えられます。例えば以下のようなSQL文です。
SELECT
日付,
店舗,
区分,
売上げ,
LAG(売上げ) OVER (
PARTITION BY 店舗, 区分
ORDER BY 日付
) AS 前年度売上
FROM テーブル名
ORDER BY 日付, 店舗, 区分;
なお、前回の投稿で一つ補足し忘れていましたが、最後にピボットテーブルにした際、小計はデフォルトだとメジャー（この場合、前年度売上げ）と同じ集計となってしまうため、「合計」を選択することで正しい数値が出ます。
@chiemi.shimoda 改めてご確認のほどよろしくお願いします。
1 Like
