2 Replies Latest reply on May 4, 2018 11:43 AM by Soumya Tejas

    LOD and Mixing Average/Non-Average fields

    Soumya Tejas

      Dataset Summary:

      I have Individual ID along with Year, Area and Loan Type dimensions, and the amount of loan they borrowed as the measure.

      Loan_ID=0 refers to people with no loan

       

      Objective:

      For selected Year (AYR) and Area_ID, display the below for each Loan_ID:

      1. Total number of individuals in that population

      2. Individuals with loan in that population

      3. Percentage of individuals with loan in that population

       

      **One individual may have more than one type of loan borrowed in the same year as well... so i am using countd here...

       

      Issue:

      1. I used the below formula to get Total # of individuals in that population (field name is Population Strength):

      {FIXED [AYR],[Area Id]:COUNTD([IID])}

      When i put this value against Loan_IDs in the same population setting, it gives me different value for each line. This should actually be a constant value for the filter settings, irrespective of the loan_id.

       

      2. I try to get the Percentage of Individuals with Loan in that population as:

      Individuals with loan / Population Strenght

      where Individuals with loan is:

      IF [Loan Amt]>0 THEN [IID] ELSE NULL END

      But, Tableau gives me the below error when I try to do this:

      "Cannot mix aggregate and non-aggregate arguments with this function"

       

      Can someone please help me figure out how to fix this? I am attaching the workbook for reference.

       

       

      Thanks!

        • 1. Re: LOD and Mixing Average/Non-Average fields
          Jim Dehner

          Hi

          I'm not certain I understand what you expect but see the attached

           

          the total LOD is

           

          a the percent is

           

          it returns this note - the totals include the 0 values - did know if wanted them removed

           

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: LOD and Mixing Average/Non-Average fields
            Soumya Tejas

            Thank you, Jim...

             

            The issue with percentage is fixed. However, there are issues with Population Strength. Yes, i need to include the count of individuals with no loan in overall population strength. the problem comes with below.

             

            I have two kind of filters,

            SET 1 - one will decide the population (like AYR and Area_ID in the example i shared earlier)

            and other SET 2 - will decide how many students got the loan, what was the % etc...

             

            Population strength should not get affected by SET 2 filters.

             

            In my real case,

            SET 1 is AYR, Class Group, Post ID, Owning Unit, Program Name

             

            when i use

             

            I get:

             

            When i use:

            I am getting something like below

             

            The correct value for total by post setting in this combination i have set is 84.

             

            I hope this helps in understanding the problem better. Please let me know if you are able to suggest why it is happening so. I was expecting all the row values for Total in this second case to be 84...

             

            Thanks!