2 Replies Latest reply on Sep 23, 2016 10:57 AM by sam.hong.0

    How to do a combine count on two categories?

    sam.hong.0

      Hi,

       

      I have a total of three categories in my data-set: Television, Internet and Word of Mouth.

       

      I would like to plot the data similar to this on one chart:

       

      Television = Red

      Internet = Blue

      Word of Mouth = Green

      Internet + Word of Mouth = Purple

       

      However, I can't seem to find a way to create a formula that would combine Internet and Television and count the number of records.

       

      I tried the following:

       

      SUM(IF [Source of Report]="Television" THEN [Number of Records]

      ELSEIF [Source of Report]="Internet" THEN [Number of Records]

      END)

       

      When I do this, it only excludes "Word of Mouth" from my data and doesn't combine Television and Internet number of records.

       

      the below graph would be a time series chart showing the number of records per month. I attached the excel spreadsheet if someone could take a quick look.

       

       

        • 1. Re: How to do a combine count on two categories?
          Benjamin Greene

          Here's an idea:

           

          1. Create a calculated field called Internet + Word of Mouth as:

           

          IF [Source of Report]="Internet"

          OR [Source of Report]="Word of Mouth"

          THEN 1

          ELSE 0

          END

           

          2. Drag SUM(Number of Records) and SUM(Internet + Word of Mouth) to Rows.

          3. Right-click the SUM(Internet + Word of Mouth) pill and select Dual Axis.

          4. Right-click the Internet + Word of Mouth axis and select Synchronize Axis.

          5. Right-click the Internet + Word of Mouth axis and deselect Show Header.

          6. Right-click drag Date Reported to Columns and select Date Reported (Discrete).

          7. In the All Marks card, remove Measure Names from Color.

          8. In the SUM(Number of Records) Marks card, drag Source of Report to Color.

          9. Click Color>Edit Colors to set the Internet, Television and Word of Mouth colors to be whatever you like.

          10. In the SUM(Internet + Word of Mouth) Marks card, click Color to set that line's color to be whatever you like.

          • 2. Re: How to do a combine count on two categories?
            sam.hong.0

            Thank you Benjamin!!!

             

            It worked!

             

            Sam