6 Replies Latest reply on Oct 24, 2018 2:23 AM by Sujeet kumar

    To get data for particular Month

    Sujeet kumar

      I want to find the average of a particular month

      Example:  I have applied this formula :   AVG({INCLUDE [Month]= 'July': SUM([Collection])/ SUM([No.Of Vehicles])}) and write this formula on ROW in tableau

       

       

       

      I have Month column(June, July, August). I want to find per vehicle average of month July. So I would divide the sum of the collection in July to no. of vehicles in July.

       

      What is the wrong with the above formula?

        • 1. Re: To get data for particular Month
          Hideki OGAWA

          Hi Sujeet,

           

          It would be helpful if you attache the sample workbook and/or sample data for this.

          I'm afraid Collection and No.Of Vehicles should be dimensions which cannot be aggregate by SUM, you should use COUNTD instead to count measures.

          And I'm not sure what sum(collection)/sum(No. Of Vehicles) means either, so I cannot say that calculation is collect.

           

           

          Best regards,

          Hideki OGAWA

          • 2. Re: To get data for particular Month
            Sujeet kumar

            This is the sample data. What I want to calculate collection per vehicle for a particular month.

            For example June: total collection: (4500+234+314253+32435+57+89876+ 1234+213+12425) = 455227

                                          Total Vehicle June= 9

                                           Collection/Vehicle for June= 50580

             

            So what should I write on the row of tableau to get this?

             

             

             

            Screen Shot 2018-10-23 at 2.43.29 PM.png

            • 3. Re: To get data for particular Month
              Hideki OGAWA

              Then both of them are measure.

              I believe you can do that without LOD, SUM(A)/SUM(B) and fliter by month to July, it's simple.

              Or you can use LOD {FIXED [Month]: SUM(A)/SUM(B)} and filter by month to July, but I don't understand why you need this.

              I have attached the sample workbook using data aggregated from Superstore data.

              Is that what you want?

              Please let us know you want something different.

               

               

              Best regards,

              Hideki OGAWA

              • 4. Re: To get data for particular Month
                Sujeet kumar

                I want to compare the revenue/vehicle of month August to an average of revenue/vehicle of June and July together.  I want two line in a graph one will show revenue/vehicle of August and other will show average revenue/ vehicle of June and July together.

                This could then be averaged across another dimension (eg. venue then car type then model) when dragged into the view.

                • 5. Re: To get data for particular Month
                  Hideki OGAWA

                  Then it should be like this, to allow any dimension to jump in;

                   

                  AVG({INCLUDE [Month]:SUM([Sales])/SUM([Quantity])})

                   

                  This is based on the assumption you need June and July together but want to calculate average of revenue/vehicle of both months.

                  If you just want to combine June and July, just SUM(A)/SUM(B) should be enough.  Please see the difference between the two types of calculation.

                   

                   

                  Best regards,

                  Hideki OGAWA

                  • 6. Re: To get data for particular Month
                    Sujeet kumar

                    Thanks for the reply. If I want to find the average for July month only then I should write :

                     

                    AVG({INCLUDE [Month]= 'July' :SUM([Sales])/SUM([Quantity])})  or something else.