7 Replies Latest reply on Feb 11, 2019 9:13 AM by Ken Flerlage

    Calculated Field Aggregate/Non-Aggregate Error

    Emily Herbst

      Hello all,

      I am trying to recreate some data I previously analyzed using SAS. As an end, I am trying to calculate a rate of claims accounts divided by comp accounts

      count(distinct accno)/count(distinct accountnumber)

       

      Originally my code was set up to join as follows:

       

       

       

      proc sql;

      create table testjoin as

      select *

      from compaccts a

      left join unionall b

      on a.accountnumber=b.accno

      and datepart(b.claimdate) - datepart(a.compstartdate)<=120

      and a.compstartdate <= b.claimdate;

      quit;

       

      Tableau does not appear to allow the last two sections of my join statement to join across tables from different servers (which mine are) so I have adjusted my code as follows:

       

      select *

      from compaccts a

      left join unionall b

      on a.accountnumber=b.accno

       

      now I am trying to implement the date criteria from my original code into my rate field calculation, but am getting an error stating I cannot mix aggregate and non-aggregate comparisons. Does anyone know how I can adjust the following query?

       

       

      if ([compstartdate]<= [claimdate]) and ([claimdate]-[compstartdate])<=120

      then countd([accno])/countd([accountnumber]) else NULL end

        • 1. Re: Calculated Field Aggregate/Non-Aggregate Error
          Naveen B

          Hi Emily,

           

          Change your calculation like below

           

           

          countd(if ([compstartdate]<= [claimdate]) and ([claimdate]-[compstartdate])<=120 THEN [accno] ELSE NULL END)/

          countd(if ([compstartdate]<= [claimdate]) and ([claimdate]-[compstartdate])<=120THEN [accountnumber]ELSE NULL END )

           

          OR

           

          if ATTR([compstartdate])<= ATTR([claimdate]) and ATTR([claimdate])-ATTR([compstartdate])<=120

          then countd([accno])/countd([accountnumber]) else NULL end

           

          Hope this helps.

          If it helps kindly mark this answer as correct or helpful to close the thread

           

          BR,

          NB

          • 2. Re: Calculated Field Aggregate/Non-Aggregate Error
            Ken Flerlage

            It's always difficult to troubleshoot these without seeing the workbook, but the problem here is that you are aggregating the value you're returning but your IF statement includes fields that are not aggregates. I'd probably suggest breaking this into two separate measures:

             

            Accno Count

            IF ([compstartdate]<= [claimdate]) AND ([claimdate]-[compstartdate])<=120 THEN

                 [accno]

            END

             

            Accountnumber Count

            IF ([compstartdate]<= [claimdate]) AND ([claimdate]-[compstartdate])<=120 THEN

                 [accountnumber]

            END

             

            Then you can create a third to perform the math & aggregation:

             

            Ratio

            COUNTD([Accno Count])/COUNTD([Accountnumber Count])

            1 of 1 people found this helpful
            • 3. Re: Calculated Field Aggregate/Non-Aggregate Error
              Mihai Constantinescu

              If claimdate and compstartdate are Date&Time I would suggest using DATEDIFF() and specify if you want the difference in hours or minutes.

              • 4. Re: Calculated Field Aggregate/Non-Aggregate Error
                Emily Herbst

                Thank you, I think this is all helpful, but I think I am realizing there is a different issue at play... the way the rate is being calculated with all these suggestions (and my previous attempts) appear to be calculating the rate on the row level and thus I keep getting a row level value for rate as 0 or 1. Since my data are already joined on accountnumber=accno it is currently just calculating when the account is present in both tables 1/1=1. null/1=null. What I want is an overall rate. I will try to give some sample data for example:

                 

                Type         accountnumber    compstartdate      compid   |   accno      claimdate        rate

                credit               123                    12/1/2018             5             123         1/30/2019       0.67    (= 2/3)

                credit               456                    12/2/2018             6             456         1/20/2019       0.67    (= 2/3)

                credit               789                    12/3/2018             7              null         null                 null      (doesn't join)

                debit                 999                    07/4/2018            8              999          1/30/2019      null      (more than 4 month diff)

                debit                  998                   12/4/2018             9              998          1/15/2019       0.75    (=3/4)

                debit                  997                   12/5/2018            10             997          1/14/2019       0.75    (=3/4)

                debit                  996                   12/10/2018           11            996          1/12/2019       0.75    (=3/4)

                 

                The rate should come from how many valid accno joined / the total of accountnumber (regardless of accno matching on the join) and I would like this grouped within each Type. So two valid accno claims in credit/credit accountnumber =0.67 and same for debit.

                 

                Thank you all for your help.

                • 5. Re: Calculated Field Aggregate/Non-Aggregate Error
                  Ken Flerlage

                  So, if I understand correctly, you want the denominator to be the number of accounts, regardless of the date criteria. In that case, use these calculated fields:

                   

                  Accno Count

                  IF ([Compstartdate]<= [Claimdate]) AND ([Claimdate]-[Compstartdate])<=120 THEN

                       [Accno]

                  END

                   

                  Ratio

                  COUNTD([Accno Count])/COUNTD([Accountnumber])

                   

                  That should give you what you're expecting:

                  See attached.

                  1 of 1 people found this helpful
                  • 6. Re: Calculated Field Aggregate/Non-Aggregate Error
                    Emily Herbst

                    Thank you so much for your help!

                    • 7. Re: Calculated Field Aggregate/Non-Aggregate Error
                      Ken Flerlage

                      Any time. If my response has answered you question, would you be so kind as to mark it as the "correct answer"? This will allow us to close the thread and will make it easier for people to find the answer to similar questions in the future. Thanks!