6 Replies Latest reply on Nov 11, 2016 2:17 AM by Alex Braga

    cannot mix aggregate and non-aggregate comparisons or results in 'if' expression

    Alex Braga

      Hi all,

       

      I'm trying to calculate some metrics for a particular time period (say 2015 and 2016). This is a healthcare exercise, and the variable I'm calculating is called "Procedures per Claim". For this variable I want to calculate and compare:

       

      [1.PROCS_CLAIM_Previous]: Procedures per Claim at a point in time (let's use 3 months for this example) in 2015

      [2.PROCS_CLAIM_Current]: Procedures per Claim at same point in time (same 3 months) in 2016

      [3.PROCS_CLAIM_Diff]: The difference in [Procs_per_Claim] for the time periods

      [4.PROCS_CLAIM_Percent_Diff]: The % difference in [Procs_per_Claim] for the time periods

       

       

      In order to do this I have Filters, Parameters and Variables defined as follows:

       

      [Procs_per_Claim]: measure. Defined as:

      SUM([Proc Count])/COUNTD([CLAIMID])

      Where: [Proc Count] = count of procedures, [CLAIMID] =  individual claim numbers

       

      [Current or Previous]: Dimension. Defines the time periods to be evaluated, IF Statement as follows:

       

      //If the period is LM or YTD, then if the year of the order date is the same as the year of the

      //selected month,it's the current period, otherwise it's the previous period

      if [Period] <3 then IIF(year([SVCBEGDATE]) = year([SVCBEGDATE (MONTHS) PARAMETER]),1,0)

      //If the period is L3M, then if the difference in months is less than 3 it's the current period,

      //otherwise it's the previous period

      elseif [Period]=3 then  IIF( [Difference in Months] <3 , 1, 0)

      //If the period is L12M, then if the difference in months is less than 12 it's the current period,

      //otherwise it's the previous period

      else IIF( [Difference in Months] < 12, 1, 0)

      end

       

      [Difference in Months]: Measure. Shows the number of months from Service Begin Date [SVCBEGDATE] to a number of months specified by the parameter [SVCBEGDATE (MONTHS) PARAMETER]

      DATEDIFF('month', [SVCBEGDATE], [SVCBEGDATE (MONTHS) PARAMETER])

       

      [1.PROCS_CLAIM_Previous]: Measure. Should be the [Procs_per_Claim] records for the previous period (2015)

      if ATTR([Current or Previous]) = 0 then [Procs_per_Claim] end

       

      [2.PROCS_CLAIM_Current]: Measure. Should be the [Procs_per_Claim] records for the current period (2016)

      if ATTR([Current or Previous]) = 1 then [Procs_per_Claim] end

       

      [3.PROCS_CLAIM_Diff]: Measure. Should be the difference in [Procs_per_Claim] for the two time periods

      [2.PROCS_CLAIM_Current]-[1.PROCS_CLAIM_Previous]

       

      [4.PROCS_CLAIM_Percent_Diff]: Measure. Should be the % difference in [Procs_per_Claim] for the two time periods

      ([2.PROCS_CLAIM_Current] - [1.PROCS_CLAIM_Previous]) / [1.PROCS_CLAIM_Previous]

       

      ****************************************************

      RESULTS (OR LACK THEREOF)

       

      As I drag [1.PROCS_CLAIM_Previous] and [2.PROCS_CLAIM_Current]) to the rows shelf I get no results. The formulas for the difference and percent difference are also wrong and not returning any values.

      I am running into aggregation errors, and I'm not sure what I am missing.

       

      By the way, the code above is working just fine for a [PAID] measure. But when substituting [PAID] for [Procs_per_Claim] the calculations fall apart due to aggregate errors.

       

      Your help and and expertise would be highly appreciated. What I am missing?

       

      Thanks!

        • 1. Re: cannot mix aggregate and non-aggregate comparisons or results in 'if' expression
          John Croft

          Sample data would be appreciated. Other than that, I'd just start smaller and making sure each piece you build works. Once you find something not working, trouble shoot it be wrapping things in the appropriate aggregate function ATTR(), SUM(), MIN(), &c....

          • 2. Re: cannot mix aggregate and non-aggregate comparisons or results in 'if' expression
            Alex Braga

            Thanks John,

             

            Unfortunately the dataset for this is massive and contains private info. So I can't sent my file. But what I'm doing is based on this thread (tableau file attached): Current vs Previous Period to Date Comparison

            For the file attached you have:

             

            [Current Period to Date Selection]: defined as

            IF

                (DATETRUNC([Period], [Date]) =

                DATETRUNC([Period], [Date Selection])

                AND

                DATETRUNC('day', [Date]) <=

                DATETRUNC('day', [Date Selection]))

            THEN

                [Value]

            END

             

            The difference between his case and my case is that for him [Value] is an amount to be aggregated. (Remember that I said that when I plug [Paid] in my equations, the formulas work fine)

            In my case, on the other hand, [Value] is replaced with [Procs_per_Claim], which is a rate, defined as SUM([Proc Count])/COUNTD([CLAIMID]); where: [Proc Count] = count of procedures, [CLAIMID] =  individual claim numbers.

             

            So I'm running into aggregation issues. Thanks for your help.

            • 3. Re: cannot mix aggregate and non-aggregate comparisons or results in 'if' expression
              John Croft

              You need to aggregate to portion between if and then so that the aggregate calculation will process.

               

                ATTR (DATETRUNC([Period], [Date]) =

                  DATETRUNC([Period], [Date Selection])

                  AND

                  DATETRUNC('day', [Date]) <=

                  DATETRUNC('day', [Date Selection]))

               

              In the workbook you sent, I created this variable below to somewhat be what your procs_per_claim is.

               

               

              And then I fed it into the calculation instead of 'value'. And then wrapped the date logic with ATTR like this - notice no error message.

               

               

              Hope this helps.

              2 of 2 people found this helpful
              • 4. Re: cannot mix aggregate and non-aggregate comparisons or results in 'if' expression
                Alex Braga

                Hi John, I really appreciate your help! Thanks so much for your replies!

                 

                Since my formula to determine the time frame window comparison (current or previous) is a bit different, the ATTR() on the if statement didn't work for me. Thanks for the suggestion tho!

                 

                *******************

                Let me put some more info here for you guys. Below is an example of these calculation being done for [Proc Count] which is the count of procedures, coming directly from the dataset (not a calculated field)

                 

                [1.PROCS_Previous] defined as:

                if [Current or Previous] = 0 then [Proc Count] end

                 

                [1.PROCS_Current] defined as:

                if [Current or Previous] = 1 then [Proc Count] end

                 

                [3.PROCS_Diff] defined as:

                sum([2.PROCS_Current])-sum([1.PROCS_Previous])

                 

                [4.PROCS_Percent_Diff] defined as:

                (sum([2.PROCS_Current]) - sum([1.PROCS_Previous])) / sum([1.PROCS_Previous])

                 

                 

                Where:

                [Current or Previous] defined as the time periods to be evaluated, IF Statement as follows:

                 

                //If the period is LM (1) or YTD (2), then if the year of the order date is the same as the year of the

                //selected month,it's the current period, otherwise it's the previous period

                if [Period] <3 then IIF(year([SVCBEGDATE]) = year([SVCBEGDATE (MONTHS) PARAMETER]),1,0)

                //If the period is L3M (3), then if the difference in months is less than 3 it's the current period,

                //otherwise it's the previous period

                elseif [Period]=3 then  IIF( [Difference in Months] <3 , 1, 0)

                //If the period is L12M (4), then if the difference in months is less than 12 it's the current period,

                //otherwise it's the previous period

                else IIF( [Difference in Months] < 12, 1, 0)

                end

                 

                [Difference in Months]: Defines the number of months from Service Begin Date [SVCBEGDATE] to a number of months specified by the parameter [SVCBEGDATE (MONTHS) PARAMETER]

                DATEDIFF('month', [SVCBEGDATE], [SVCBEGDATE (MONTHS) PARAMETER])

                 

                Results:

                 

                COUNT_OF_PROCEDURES.JPG

                 

                **********************************

                 

                OK. Now below are the calculations for [Procs_per_Claim] which is a calculated field. The calculations for the previous and current periods are actually correct, but the display won’t match as above. Which makes me think I may be missing something super simple.

                 

                [1.PROCS_CLAIM_Previous] defined as:

                if MIN([Current or Previous]) = 0 then [Procs_per_Claim] end

                 

                [2.PROCS_CLAIM_Current] defined as:

                if MIN([Current or Previous]) = 1 then [Procs_per_Claim] end

                 

                [3.PROCS_CLAIM_Diff] wrongly defined as:

                [2.PROCS_CLAIM_Current]-[1.PROCS_CLAIM_Previous]

                 

                [4.PROCS_CLAIM_Percent_Diff] wrongly defined as:

                ([2.PROCS_CLAIM_Current] - [1.PROCS_CLAIM_Previous]) / [1.PROCS_CLAIM_Previous]

                 

                When I add the date variable to the columns shelf, it shows the correct calculations for the Procedures per Claim rates. Note that in the example above I didn't have to add the date variable in order to show the current and previous years.

                 

                procs_claims_right.JPG

                 

                But when I remove the date variable from the columns shelf, results get messed up.

                 

                procs_claims_wrong.JPG

                 

                 

                Bummer...   Your help is still very much appreciated.

                 

                Thank you!

                 

                Alex

                • 5. Re: cannot mix aggregate and non-aggregate comparisons or results in 'if' expression
                  John Croft

                  Yes, you have a lot of manual calculations going on here. If you could stip out the private / sensitive data - names, SSN, Addresses, &c... and post some (even if dummy data, I could probably work through this for you a different way.

                   

                  For example these two - you should just use table calcs. Difference From and Percent Different. I don't think you should have to manually calculate this.

                   

                  You may also explore Level Of Detail calcs.

                  • 6. Re: cannot mix aggregate and non-aggregate comparisons or results in 'if' expression
                    Alex Braga

                    Hi guys,

                     

                    I have some data for you now. I'm presenting you with 3 scenarios. 1 of them works, the other 2 need some tweaking.

                     

                    1 - First case is for the variable PAID, and I present 2 ways of doing the exercise that works great.

                    2 - Second case is for a measure that is a calculated field, a rate called Procedures per Claims. Tweaking is needed.

                    3 - Third scenario is for another rate, PMPM (Cost per member per month), which uses data blending to pull membership.  Tweaking is also needed.

                     

                    I have added comments in the tableau file itself, and I have a quick dashboard of how I envision this thing to look like for many metrics.

                     

                    Hopefully we will all learn something cool from this. Again, thank you so much for you guys' help.

                     

                    Alex