5 Replies Latest reply on May 17, 2016 11:30 AM by B.G P

    Data blending summation issue

    B.G P
    B.G P May 10, 2016 1:34 PM

      I am facing one issue while aggregating the data from date to month rollup on data blending of two different sources. When I am rolling up data which is % based from date level to month level, It is not aggregating based on date at month level. See below requirement to get clear idea.

       

      Month of Date opened, Day of Date Opened, Full/Partial & Percent impacted user are coming from EXCEL data source (Primary Data source). The Total User is coming from Google Analytic (Secondary Data Source). Whereas “User impacted by percent” is the calculated field created based on these data source.

       

      Here is the formula of User impacted by percent:

       

      if SUM([Percent impacted])=1

      THEN SUM([GA (GA_Dataset)].[User])

      ELSE (SUM([GA (GA_Dataset)].[User])*SUM([Percent impacted]))

      END

       

      I tired ATTR in above formula instated of SUM(Percent impact) but didn’t help me. See attached workbook sample for detailed understanding.

       

      Data at date & month level:

      Month of Date Opened

      Day of Date Opened

      Full/Partial

      Percent impacted User

      User impacted by percent

      Total user

      Feb-16

      05-Feb-16

      Partial

      1%

      51

      5194

      15-Feb-16

      Partial

      50%

      1,802

      3604

      Mar-16

      03-Mar-16

      Partial

      1%

      69

      6953

      23-Mar-16

      Full

      100%

      6,049

      6094

       

      Expected result

      Month of Date Opened

      Full/Partial

      Percent impact

      User impacted by percent

      Total User

      Feb-16

      Partial

      51%

      1853

      8798

      Mar-16

      Partial

      1%

      69

      6953

      Full

      100%

      6,049

      6094

       

      This is what I am getting after removing date dimension from worksheet. The “User impacted by percent” is doing sum first and then do % on top of that data and showing here instead of above mentioned dates of Feb.

      Month of Date Opened

      Full/Partial

      Percent impact

      User impacted by percent

      Total User

      Feb-16

      Partial

      51%

      4486

      8798

      Mar-16

      Partial

      1%

      69

      6953

      Full

      100%

      6,049

      6094

       

      Thanks in advance..

        • 1. Re: Data blending summation issue
          Adam Cogswel
          Adam Cogswel Employee May 13, 2016 3:08 PM (in response to B.G P)

          Try Changing your aggregation from SUM to MIN for User under Measure Values, and also in your formula for % User Impacted, so you end up with this:

           

          if SUM([Percent impacted])=1

          THEN MIN([GA (GA_Dataset)].[User])

          ELSE (MIN([GA (GA_Dataset)].[User])*SUM([Percent impacted]))

          END

          • 2. Re: Data blending summation issue
            B.G P
            B.G P May 17, 2016 6:20 AM (in response to Adam Cogswel)

            Thanks Adam for your response.

             

            I tried both MIN & MAX functions instead of SUM but no luck.

             

            Thanks

            • 3. Re: Data blending summation issue
              Virginia Madden
              Virginia Madden May 17, 2016 6:46 AM (in response to B.G P)

              I'm a Newbie, too, so you may need to take this with a huge grain of salt....a salt mine, perhaps? 

               

              It looks like you are wanting to add up the "percent impacted" first before the other calculations are done.  Is that correct?

               

              If so, have you tried adding another set of parentheses around that part of your formula so that Tableau will do that particular calculation first?  This way

              SUM([Percent impacted]))   (I realize the last parenthesis actually goes with another part of the formula, but I want to be clear.)

               

              becomes

              (SUM([Percent impacted])))

               

              in your formula.

               

              Of course, I may not know what the heck I'm talking about, either (note Newbie status) but just trying to help.  Please forgive me if I'm totally off base or if this idea is stupid.

              • 4. Re: Data blending summation issue
                Adam Cogswel
                Adam Cogswel Employee May 17, 2016 8:35 AM (in response to B.G P)

                Oh I see. Could you include the Full/Partial field in the GA Dataset? Then you could blend on both, and it should do what you want.

                • 5. Re: Data blending summation issue
                  B.G P
                  B.G P May 17, 2016 11:30 AM (in response to Adam Cogswel)

                  @ Virginia - I tired this at first time but not working.

                   

                  @Adam - I added the filed you suggested in my workbook & It is working fine when I have Date(Date) but not MONTH(Date). Also, I checked the same in your attached workbook too.