6 Replies Latest reply on Dec 16, 2016 12:08 PM by Jonathan Drummey

    COUNTD - Blended data

    Sebastian Cruz

      Hi everyone,

      my first attempt to blend 2 data sets was not successful (Sheet called 1st attempt - attached), because its appears the following message "Cannot blend the secondary data source because one or more fields use an unsupported aggregation". Someone in the Community told me the following:

           "MAX isn't supported in all data sources.  Check what you're using and change to something that works.

           The quick solution will be to change both data sources to extracts, but that would depend on your situation."

       

      That's why I made some changes by myself in mi workbook in order to don't use MAX and MIN (Sheet with changes called Final table)

       

       

      Now I have another problem... I am trying to make a graph, but I need to Count just one time each ID or Student warehouse Entity Uid. The problem is that t I can't use COUNTD because my data is blended, and I don't want to extract the data. (See workbook attached - it is just a sample)

      Checking on the web, I realise that COUNTD is not supported for blended data, so I was wondering what can I do in this case?

       

       

      Reviewing my data, I was thinking that maybe works if I assign a number to each row per Student warehouse Entity Uid (I created a new column in excel, so you can understand my logic - column DW), then I can filter just all the 1 values in the graph... but I was trying to do this in Tableau without success (it is possible?)

       

       

       

       

      I will appreciate your support to try to find a way to COUNTD my field Student warehouse Entity Uid for blended data.

      Thanks in advance,

       

      Sebastian Cruz

       

      Note: The Excel and Tableau files are just a sample to understand my calculations - My screenshots are of the real report.

        • 1. Re: COUNTD - Blended data
          Shinichiro Murakami

          Sebastian,

           

          Seems like your packaged workbook is not extracted.

          Could you extract data, then attach packaged workbook.

           

          Thanks,

          Shin

          • 2. Re: COUNTD - Blended data
            Sebastian Cruz

            Hello Shinichiro,

            I have updated my packaged workbook.

             

            Thanks,

            • 3. Re: COUNTD - Blended data
              Shinichiro Murakami

              Thank you Sebastian

               

              Looks like I need some time to analyze.

              What is the % on the graph picture (give me formula)

               

               

              Thanks,

              Shin

              • 4. Re: COUNTD - Blended data
                Sebastian Cruz

                The % is just the % by column.

                 

                For example, if we have the following values in the first column 090R to 1030, 1040, 1050:

                (0,1) years = 10 UID or Student Warehouse Entity UID

                (1,2) years = 10 UID

                (2,3) years = 20 UID

                (>3) years = 10 UID

                In progress = 50 UID

                The % will be:

                (0,1) years = 10%

                (1,2) years = 10%

                (2,3) years = 20%

                (>3) years = 10%

                In progress = 50%

                *Note that we can't count the same UID more than once. That's why we need to use COUNTD.

                Thanks for your time.

                • 5. Re: COUNTD - Blended data
                  Yuriy Fal

                  Hi Sebastian,

                   

                  Why not just do a cross-datasource join instead of a blend --

                  taking into account that your Data SQL table has just one row per UID?

                  You may even do a Full Outer Join -- to have both datasources at full.

                   

                  Yours,

                  Yuri

                  • 6. Re: COUNTD - Blended data
                    Jonathan Drummey

                    Hi Sebastian,

                     

                    Your post said you heard or read that "MAX() isn't supported in all data sources", and "COUNTD is not supported for blended data". I'm pretty sure that the statement about MAX() isn't correct, I've never heard of a data source that didn't support MAX(),  the possibly unsupported functions are typically the "non-additive aggregates" of COUNTD(), MEDIAN(), and PERCENTILE().

                     

                    The second statement is incorrect. Data blending *does* support COUNTD(), MEDIAN(), and PERCENTILE(), but under somewhat limited circumstances. If you have a secondary dimension on the Filters Shelf, on Rows, Columns, Pages, or the Marks Card, or are using linking dimensions that aren't in the view then you will get the unsupported aggregation error. From your second screenshot I can see the ENTRY_TERM secondary dimensions on the Filters Shelf and that would cause the COUNTD() to fail.

                     

                    See Why is COUNTD(Customer Name) red? | Drawing with Numbers for a longer explanation of the reasons behind this.

                     

                    Now Yuri's notion of a join is great, if you can do that then go for it. In Tableau v10.0 we now have cross data source joins to make life even easier. *However* Tableau cross data source joins don't yet support Tableau Server published data sources and I can see one of those in a screenshot, so you'd need to work around that.

                     

                    If you can't do either of those options then sometimes we can get around it by increasing the level of detail of the view (by putting the dimension that we were going to do a COUNTD() on) and then using something like IF FIRST()==0 THEN SIZE() END instead of a COUNTD(), but I don't know your data or goal well enough to know whether that is feasible in this case.

                     

                    Jonathan