8 Replies Latest reply on Jan 18, 2018 8:42 PM by Shinichiro Murakami

    複雑な前年比の作成について

    Maho Miyaji

      皆様 いつもお世話になっております。

       

      売上実績のデータと前年対応日表のデータを使用して前年比が分かるグラフを作成したいと思っています。

      同一の実績データをブレンド機能で結合し、今年分と前年分と分けて考えるやり方で進めています。

      これは、単純に1年前と比較するのではなく、曜日祝日を加味し、今年分と前年分を比較したいためです。

       

       

      そこで質問なのですが、

      「昨年は扱っていたが今年は扱っていない商品」があった場合に、

      同じシート内で店毎や商品毎に前年比を出すことは可能でしょうか。

       

       

      以下添付したsampleデータに基づいて説明させて頂きます。

      問題になるのは渋谷の八百屋Aで2017年に売っていた「りんご」です。

      ブレンド機能で「商品」を結合してしまうと前年しかデータのない「りんご」の売上が欠落してしまい、

      所在地毎や店名毎にフィルターをかけたときに前年売上,前年比が間違った値になってしまいます。

       

      一方で、「商品」の結合を外してしまうと商品毎のフィルターはかけられなくなってしまいます。

       

       

      なにかいい方法はありますでしょうか。

      ご教授の程、宜しくお願い致します。

        • 1. Re: 複雑な前年比の作成について
          Shinichiro Murakami

          MIyajiさん

           

          このロジックを計算式に反映させるか、すべての日付に参照テーブルを準備しないいけないと思います。

          これは、単純に1年前と比較するのではなく、曜日祝日を加味し、今年分と前年分を比較したいためです。

           

          ここを詳しくご説明いただければ、計算式あるいはJoinに変換できるかと思いますが、可能でしょうか。

           

          曜日を基準にするとかなら、計算式で対応可能だと思います。

           

          テーブルをみるかぎり、店舗、所在地によって比較対象が変わるということでしょうか?

          だとすると、組み合わせごとに参照テーブルが必要です。

           

           

          ちなみに、ブレンドでは、片方をマスタに指定しないといけないので、マスタ側にないものはもれていしまい、その回避策はありません。

           

          Shin

          • 2. Re: 複雑な前年比の作成について
            Maho Miyaji

            Shinさん

             

            さっそくのご回答ありがとうございます。

             

             

            仰る通り、所在地ごとに定休日やセール期にズレがあるため比較対象の日時が異なっています。

            だとすると日付の組み合わせごとに参照テーブルが必要とのことですが、

            SQL等で新たにデータを作成する必要があるということでしょうか。

             

            どのようなテーブルが必要なのかお教え頂けますと幸いです。

             

            宜しくお願い致します。

             

            Miyaji

            • 3. Re: 複雑な前年比の作成について
              Shinichiro Murakami

              miyajiさん

               

              店舗ごとに、普通のカレンダーと異なる、店舗カレンダーが必要です。

              たとえば、こんな感じ。

               

               

              DateYearABC
              2017/1/62017Day-1NADay-1
              2017/1/72017Day-2Day-1Day-2
              2017/1/82017Day-3Day-2Day-3
              2017/1/92017Day-4Day-3NA
              2017/1/102017Day-5Day-4Day-4
              2017/1/112017NADay-5Day-5
              2017/1/122017Day-6Day-6Day-6
              2017/1/132017Day-7NADay-7
              2017/1/142017Day-8Day-8Day-8
              *
              *
              *
              *
              *
              *
              2018/1/62018Day-4Day-3NA
              2018/1/72018Day-5Day-4Day-4
              2018/1/82018NADay-5Day-5
              2018/1/92018Day-6Day-6Day-6
              2018/1/102018Day-7NADay-7
              2018/1/112018Day-8Day-7Day-8
              2018/1/122018Day-9Day-8Day-9
              2018/1/132018Day-10Day-9Day-10
              2018/1/142018Day-11Day-10Day-11

               

               

              店舗とDateをキーにして、Day-x どうし(2017 Day-4 と 2018 Day-4)を比較していくことになります。

              月ごとなのか年ごとなのか、週ごとなのか分かりませんが、比較したい日付同士が同じ Day-X(年度だけが異なる)になるカレンダーがいります。

               

              Shin

              • 4. Re: 複雑な前年比の作成について
                Maho Miyaji

                Shinさん

                 

                教えて頂いたように下のような店舗カレンダーのデータを作成して再度チャレンジしています。

                元データとして使用しているのは添付したエクセルデータ3つです。

                店舗とDateをキーにして、Day-x どうし(2017 Day-4 と 2018 Day-4)を比較していくことになります。

                上記のようにDay‐xどうしを比較するという考え方自体は理解できるのですが、

                実際にワークシートを作成しようとするとどのような計算式あるいはデータjoinをすれば良いのかが分かりません(そもそも店舗カレンダーの認識が間違っているのでしょうか)。

                 

                何度もお手数おかけし恐縮ですが、

                店舗カレンダーをどのように使用して前年比のデータを作成すれば良いのか

                再度ご教授下さいますでしょうか。

                 

                 

                宜しくお願いします。

                 

                Miyaji

                • 5. Re: 複雑な前年比の作成について
                  Shinichiro Murakami

                  Miyajiさん

                   

                  まずは、何らかのツールを使って、(一例下記)カレンダーをFlatなデータにします。

                  Excel 向けの Tableau アドイン (Tableau Add-In in Excel) | Tableau Padawan's Tips & Tricks

                   

                  でも、その前に、店舗を「日本語の名前」で取り扱うのは骨が折れるので、店舗IDをつけましょう。

                   

                  売り上げ側

                    

                  所在地店名商品取引日時売上Shop ID
                  渋谷八百屋Aりんご1/6/201712598AS
                  渋谷八百屋Aりんご1/7/201728574AS
                  渋谷八百屋Aりんご1/8/201717653AS
                  渋谷八百屋Aみかん1/6/201715874AS
                  渋谷八百屋Aみかん1/7/201739578AS
                  渋谷八百屋Aみかん1/8/201730782AS
                  渋谷八百屋Aみかん1/8/201814784AS
                  渋谷八百屋Aみかん1/9/201840562AS
                  渋谷八百屋Aみかん1/10/201828743AS
                  渋谷八百屋Aぶどう1/6/201716874AS
                  渋谷八百屋Aぶどう1/7/201728578AS
                  渋谷八百屋Aぶどう1/8/201730519AS
                  渋谷八百屋Aぶどう1/8/201812964AS
                  渋谷八百屋Aぶどう1/9/201839762AS
                  渋谷八百屋Aぶどう1/10/201825847AS
                  池袋八百屋Aいちご1/9/201727561AI
                  池袋八百屋Aいちご1/10/201730079AI
                  池袋八百屋Aいちご1/11/201728677AI
                  池袋八百屋Aいちご1/8/201826773AI
                  池袋八百屋Aいちご1/9/201830067AI
                  池袋八百屋Aいちご1/10/201827008AI
                  池袋八百屋Bぶどう1/9/20179847BB
                  池袋八百屋Bぶどう1/10/201716743BB
                  池袋八百屋Bぶどう1/11/201710874BB
                  池袋八百屋Bぶどう1/8/201811076BB
                  池袋八百屋Bぶどう1/9/201816498BB
                  池袋八百屋Bぶどう1/10/201816982BB

                    

                  カレンダー

                   

                  dateASAIBB
                  1/6/20172017Day-1NANA
                  1/7/20172017Day-2NANA
                  1/8/20172017Day-3NANA
                  1/9/20172017NADay-1Day-1
                  1/10/20172017NADay-2Day-2
                  1/11/20172017NADay-3Day-3
                  1/8/20182018Day-1Day-1Day-1
                  1/9/20182018Day-2Day-2Day-2
                  1/10/20182018Day-3Day-3Day-3

                   

                   

                  ツールを使うと、

                   

                  dateShop IDValue
                  1/6/20172017ASDay-1
                  1/6/20172017AINA
                  1/6/20172017BBNA
                  1/7/20172017ASDay-2
                  1/7/20172017AINA
                  1/7/20172017BBNA
                  1/8/20172017ASDay-3
                  1/8/20172017AINA
                  1/8/20172017BBNA
                  1/9/20172017ASNA
                  1/9/20172017AIDay-1
                  1/9/20172017BBDay-1
                  1/10/20172017ASNA
                  1/10/20172017AIDay-2
                  1/10/20172017BBDay-2
                  1/11/20172017ASNA
                  1/11/20172017AIDay-3
                  1/11/20172017BBDay-3
                  1/8/20182018ASDay-1
                  1/8/20182018AIDay-1
                  1/8/20182018BBDay-1
                  1/9/20182018ASDay-2
                  1/9/20182018AIDay-2
                  1/9/20182018BBDay-2
                  1/10/20182018ASDay-3
                  1/10/20182018AIDay-3
                  1/10/20182018BBDay-3

                   

                   

                   

                  あとは、通常(?)のJoinです。

                   

                   

                  Thanks,

                  Shin

                  1 of 1 people found this helpful
                  • 6. Re: 複雑な前年比の作成について
                    Maho Miyaji

                    Shinさん

                     

                    ご丁寧に回答頂きありがとうございました。

                    色々と便利なツールがあるんですね。

                     

                    お陰様で表とグラフを作成することができました!

                    表.jpg

                    Book1-000.jpg

                     

                    グラフの方で2017年を非表示にして「りんご」のみにフィルターをかけると表示が消えてしまいますが、2018年のデータに「りんご」が存在しない限り表示は不可能ということですよね…。

                     

                    色々と勉強になりました。

                    重ね重ねの質問にもご対応下さりありがとうございました。とっても助かりました。

                     

                    今後も精進致します。

                     

                    Miyaji

                    • 7. Re: 複雑な前年比の作成について
                      Shinichiro Murakami

                      MIyaji さん

                       

                      どういたしまして。

                       

                      で、Inboxからでなく元のポストから、以下のように、「正解」「ヘルプフル」をマークして、

                      スレッドを閉じてもらえるでしょうか。

                       

                       

                      Shin

                      • 8. Re: 複雑な前年比の作成について
                        Shinichiro Murakami

                        Miyaji さん

                         

                        昨日、余裕ありませんでしたが、表示に関しては、以下のほうが良いかもしれないです。

                        他にも言い出したらきりがないですが、ひとまずは。

                         

                         

                        Thanks,

                        Shin

                        1 of 1 people found this helpful