4 Replies Latest reply on Jun 11, 2018 1:49 AM by Shivi Bhatia

    Identifying Months Above Average Age

    Shivi Bhatia

      I am working on one of the use case where i need to find what all months had age higher than average age. The graph is in shape of a bar chart as attached below.

       

      I need to create a calculated field which tells me which months have age higher than average age and use it as on the color tab.

       

      Request assistance.

        • 1. Re: Identifying Months Above Average Age
          Naveen B

          Hi Shivi,

           

          Since you didn't provide the data set I created my Own kindly follow the below Steps to achieve your requirement

          1) Data set:

          2) Bar Graph Plot:

          3) To Calculate the Average Value and Identify what are the months more than Average

            a) One with Exclude calculation (by removing the month Granularity)

            b) Other Calculation using Fixed (Note : filter wont work until you add the filter to context)

           

           

          4) Drag the Calculated field to Color section in Marks Shelf

           

           

           

          Now you can get the Months above average in blue color and less than average in Grey-blue color. Hope this helps.

           

          If this solution didn't resolve the issue kindly attach the twbx will try to help you out.

           

          If this solution helps Kindly Marks this is question is completed so that it will help others.

           

          Thanks,

          Naveen Bandla

          • 2. Re: Identifying Months Above Average Age
            Shivi Bhatia

            Thanks Naveen.

                

            I tried the first calculation but it gives only one category as "yes" . The calc i have tried is :

             

            if SUM([Age])> AVG({EXCLUDE [Month]:AVG([Age])})

            then "No"

            else "Yes"

            End

             

            This only gives me one category as "Yes". Unfortunately as this is a client data i cant share it. I have copied the data that i am using without any other details.

             

            Please let me know if this works else i would share another file creating only from this data.

             

             

            Month Avg. Age

            April 4.252982865

            February 4.6625

            March 5.108969963

            January 5.884420736

            December 65.769354839

            November 98.259204713

            October 124.885017422

            September 264.642857143

            May 292.384615385

            August 298.363636364

            June 298.737704918

            July 319.269230769

            • 3. Re: Identifying Months Above Average Age
              Jeevan Krishna

              Hi Shivi,

               

              I am not very sure on how exclude works so I am going with the Fixed approach similar to the answer mentiooned above.

               

              From the data you pasted. Calculate the following fields

               

              This when put in dashboard looks like this.

              The calculation seems working and you can use the comparison field in color Marks

              1 of 1 people found this helpful
              • 4. Re: Identifying Months Above Average Age
                Shivi Bhatia

                Thanks Jeevan, this worked as i needed.

                 

                Could not have thought that it would have been so simple.

                 

                Regards, Shivi