3 Replies Latest reply on Mar 14, 2018 4:03 AM by Saad Daudpota

    Mixing Aggregate and non-aggregate.

    Saad Daudpota

      Hi guys, So I have been stuck on this issue for a while now, tried a lot of different, even those I didn't fully understand.

       

      So it goes like this:

       

      I have to data sets, ClaimsDS and OSDS.

       

      ClaimsDS contains the data for the particular claim, each entry is unique by the claim ID, policy ID, and date.

      OSDS contains the data for only the claims that are outstanding, each entry is unique by claim ID, policy ID, and date.

      So basically there is a one-one correspondence.

      Both the databases are on the tableau server and live.

       

      My task is to pull the amounts outstanding(OS) into the claims data and build some views, easy. Blend the data with the fields mentioned above and use sums, works.

       

      The issue that I am facing, is when I have to use boolean/binary/flag (AR) with an aggregate measure.

       

      I have a flag(AR) that tells me if there is supposed to be an anticipated recovery on the claim, it is in the ClaimsDS, and the anticipated recovery amount is the outstanding amount(OS) if the flag equals 1.

       

      Now to pull the OS amount into claims, I just the SUM function in the calculated field, and tableau uses the relationship and views to aggregate data.

       

      So I want in the same View, the total outstanding(OS), regardless of the flag, and the anticipated recovery, which basically is the outstanding amount(OS) when the flag(AR) is 1. I cant use a filter, nor an IF statement because mixing isn't allowed. How do I go about it?

       

      To summarise.

      The flag is in one database, the primary one, where all the other dimensions are present that are needed for building the views. The measure is in the other database, which is the secondary one, and is smaller, but carries less columns as well. I have to build a view with the sum of the measure in the secondary database and dimensions from the primary database. in the view, I have to show, the sum of the measure and some of the meaure with the flag.

       

      I hope I have been clear enough.

       

      I cannot attach a workbook, as of now because of the sensitivity if the data, I'd need approvals. Any push in any direction will be highly appreciated.

        • 1. Re: Mixing Aggregate and non-aggregate.
          Joydip Thakur

          I'm not fully understanding the issue without data.

           

          But did you try wrapping your "boolean/binary/flag" under ATTR() function ?

           

          Is it possible to share the calculation where you are getting the error ?

          • 2. Re: Mixing Aggregate and non-aggregate.
            Saad Daudpota

            Yes, I have tried wrapping it. it doesn't work.

            I have even tried to pull the flag into the OSDS database, and work from there, still the same problem. A cross database join would solve my problem, where the measure is static, so that tableau doesn't have to do aggregation, but that isn't possible with live tableau server data sources.

             

            the calculation has to be row level to work. If the flag is 1 then there is anticipated recovery none otherwise. But the amount of the recovery is in the other database.

             

            I can build an excel, if you want to look at the data.

            • 3. Re: Mixing Aggregate and non-aggregate.
              Saad Daudpota

              So I checked, the calculation works with the ATTR function numerous ways:

               

              IF statements

              Case

              Measure*ATTR(flag)

               

              there is a huge HOWEVER

               

              It only works when I apply a filter on that flag. otherwise the view gives 0 for that measure. I cannot have the filter.