6 Replies Latest reply on Mar 21, 2018 12:47 PM by michihiro kondo

    重複データをひとつとみなして最新日付の金額を集計する

    michihiro kondo

      初めて投稿させていただきます。

       

      まだTableauを使い始めて、間もないため、

      分かりにくいところもあるかとは思いますが、ご容赦ください。

       

      お伺いしたいことは、タイトルにある通り、重複データを除いたうえで、集計したいということです。

       

      以下の画像が元ファイルのエクセルです。

      会社名(A)、日付(10月9日)、金額(100,000)のレコードが2つ見つかるかと思います。

      これらのデータを一つとみなして集計したいと思っています。

       

      似たような質問がTableau community があり、Fixed関数を用いれば可能とのことでした。

      実際に、それを利用したところ重複データを除けることは確認できました。

       

      これに加え、日付ディメンションをシェルフにおき、

      「月」表示にしたとき、各会社の最新日付(上記画像だと10月9日)の重複データを除く金額のみを表示したいと思っています。(A社 100,000, B社50,000 C社30,000)

       

      現在、下記の式で集計をしています。

       

      ①fixed関数を用いて、重複を除く(amount dup excluded)

      { FIXED [会社],[日付]: MAX([金額])}

       

      ②最新日付のみデータのみを集計

      IF { INCLUDE : MAX([日付])} = [日付]

      THEN [amount dup excluded]

      END

                                                                                    

      会社日付摘要金額
      A10月1日りす30,000
      A10月2日すいか40,000
      A10月3日かもめ50,000
      A10月4日めだか60,000
      A10月5日からす70,000
      A10月9日すずめ100,000
      A10月9日めんたいこ100,000
      B10月9日こま50,000
      C10月9日まり30,000

       

      しかし、これらの式で計算すると、①の重複データが除かれないまま集計されてしまいます。

       

      下記の画像でA社は、100,000という金額がほしいのにも関わらず、100,000の重複データがいずれも集計され200,000が帰ってきてしまいます。

       

      駄文で大変申し訳ありませんが、皆様のご知見お借りできませんでしょうか。

      ワークブックと元のエクセルファイルを添付いたしております。

        • 1. Re: 重複データをひとつとみなして最新日付の金額を集計する
          Seiichiro Murakami

          こんばんは。

           

          私も初めて質問に返信してみます。

          至らないところもあるかと思いますがご容赦くださいm(_ _)m

           

          ちょっと発想が難しい方に流れてしまっているように見えるので、先に確認させてください。

           

          元のデータについて以下のように捉えたのですが、認識はあっていますでしょうか?

           ①「金額」列には「月初からの累計金額」が記載されている

           ②(①である前提で)同じ会社・月で考えた場合、1つ前の日付の行より金額が減ることはない

           

          もし①②ともにYESだとするならば、

          単に「MAX(金額)」を表示してあげれば求める結果が得られるのですが、これでは不十分でしょうか?

          スクリーンショット 2018-03-13 1.57.20.png

           

          ※ 偉そうに言うつもりはないですが、お調べになってみつけたFIXEDやINCLUDEを使う計算式は、

           LOD表現と呼ばれるもので、集計単位についてしっかり理解していないと、

           中級者でも躓く人が多いTableauの難所の1つです。ギターのFコード的な。。。

          1 of 1 people found this helpful
          • 2. Re: 重複データをひとつとみなして最新日付の金額を集計する
            Shinichiro Murakami

            よく分かりませんが、重複を避けるということだけに着目すると、3重くらいにフィルタをかける必要があるでしょうか。

             

            月の最後の日を会社ごとに指定。

             

            摘要を、会社ごとの月の最後の日から、とにかく一個だけえらぶ。

             

             

            会社ごとの月の最後の日の、上の摘要の金額を合計。

             

             

             

            Thanks,

            Shin

            1 of 1 people found this helpful
            • 3. Re: 重複データをひとつとみなして最新日付の金額を集計する
              michihiro kondo

              Seiichiroさん、Shinさん

               

              返信誠にありがとうございます。

              私にはない視点でしたので、非常に助かりました。

               

              >>Seiichiro

               

              わたしが用意したデータの作りがイケてなくて大変失礼いたしました。

              前提①、②についてですが、「累計」ではなく、さらに「金額」は、減ることもあります。

               

              言葉足らずで失礼いたしました。

               

              >>Shinさん

               

              fixed関数を複合して利用するという発想がなかったので、びっくりいたしました。ありがとうございます。

               

              確かに、「日付」のディメンションを「月」のときには、ほしい数値がえられております。

              ただこの場合、「日付」ディメンションを「日」に変えた途端、すべて100,000という数字が並んでしまうかと思います。

               

              ちょっと前提について加筆させていただければと存じます。

               

              「日付」ディメンションは、「日」「月」にする場合があり、それぞれの最終日(日であればそれぞれの日、月であればその月31日もしくはその月の最新日付の金額)

               

              色々、ごちゃごちゃさせてしまい申し訳ありません。

              皆様のご知見をお貸しいただければ幸いです。

              • 4. Re: 重複データをひとつとみなして最新日付の金額を集計する
                Shunta Nakajima

                Kondoさんのやりたかったことは上のお二方の回答で解決していますでしょうか?

                私も補足ということで、別のアプローチを記載してみます。

                 

                1)会社ごとのその月の最新日付の金額だけ残し、あとは捨てる

                    ⇒kondoさんの計算フィールドの①を使わずに、②で直接「金額」を入れています

                2)もし1)の結果が2行以上重複していたら、1行だけ使う

                    ⇒テキストに入れるのは「合計」ではなく「最小値」にします(「最大値」でもいいけど)

                   ※重複行の金額がすべて同じであることが前提です

                 

                金額が「月初からの累計金額」ではなく増えたり減ったりする場合はこっちかなと。

                累計だったら、無理にLOD計算を使わない明解なものである、Seiichiro Murakami さんの回答にあるアプローチで

                いけると思います。

                 

                なぜ①の計算式がうまく働かなかったについて補足します。

                計算フィールドはもとのデータの「それぞれの行に新しく列が追加される」イメージで値が計算されていくので、

                ①で重複を排除した【値】を算出したとしても、重複【行】を排除したことにならず、元データのそれぞれの行に

                ①の計算結果(会社A,10/9なら100,000)が入ることになります。

                ①の結果重複行を排除できていないわけですから、②の計算の結果、10/9の「すずめ」にも「めんたいこ」にも100,000という値が

                格納されることになり、最終的に「合計」を表示してしまうと200,000という結果になってしまいます。

                …と言葉で記載するとややこしくなってしまうので、実際にデータを並べてみたシートを添付に追加しています。

                (「データシート」タブのプレビューを見れば「それぞれの行に新しく列が追加される」イメージがつきやすいかと思いますが、残念ながらプレビューでは値が

                見えないので作ってみました)

                 

                参考になれば幸いです。

                 

                Nakajima

                • 5. Re: 重複データをひとつとみなして最新日付の金額を集計する
                  Shinichiro Murakami

                  となると、ちょっと(定義によってだいぶ)触らないといけないですが、

                   

                  「日付」ディメンションは、「日」「月」にする場合があり、それぞれの最終日(日であればそれぞれの日、月であればその月31日もしくはその月の最新日付の金額)

                   

                  太字部分、どのようにして場合が分かれるのか、(+-のドリルアップ・ダウン)

                  それともシートごと、など、詳しく説明いただけるでしょうか。

                  添付ファイルがついてるかどうか、まだ確認できないですが、添付ファイルと、

                  is、Should be の イラストがあるとわかりやすいです。

                   

                  Shin