2 Replies Latest reply on Sep 14, 2018 9:50 AM by Ken Flerlage

    Aggregate and non aggregate in tableau

    Sonali chavan

      Hello guys,

       

      I am stuck with this "cannot mix aggregate and non aggregate " issue.

      i have following things in my dashboard:

       

      1. Dynamic KPI field. While will change as KPI parameter changes. definition for that is:

      KPI All :

      CASE ([UOM])

      WHEN 'CS' then [CS]

      WHEN 'EA' then [EA]

      WHEN 'KG' then [KG]

      WHEN 'GS' THEN [GR]

      WHEN 'BS' then [BS]

      WHEN 'PC'THEN [GA]

      WHEN 'IS' THEN [IS]

      WHEN 'ST' THEN [ST]

      WHEN 'NIV' THEN [NIV]

      END

       

      2. Different calculated fields to give me my different  time frame (CY YTD, PY YTD etc) definition for which is CY YTD:,

      CY YTD:

      DATE([Calendar Day])>=  DATE(DATETRUNC('year',[Date])) and

      DATE([Calendar Day]) <=DATE ([Date])

       

      2. Dynamic time frame field which will change as time parameter changes. (YTD,MTD,Roll 12 M etc.)

      3. Then I have one measure CY which will change as per selection made in KPI parameter and time parameter. following is the definition for CY:

      CY:

      ZN(

      IF [KPI]='MTD View' and [CY MTD]  then [KPI ALL]

      ELSEIF [KPI]='Roll 12 month' and [CY ROLL 12 M] THEN [KPI ALL]

      ELSEIF [KPI]='YTD view' and [CY YTD] then [KPI ALL]

      ELSEIF [KPI]='STD QTD' and [CY STD-QTD] THEN [KPI ALL]

      ELSEIF [KPI]='PEP QTD' and [CY PEP-QTD] THEN [KPI ALL]

      ELSEIF  [KPI]='Roll 12 week' and [CY Roll 12 W] then [KPI ALL]

      ELSEIF  [KPI]='Roll 4 week' and  [CY Roll 4 W ] then [KPI ALL]

      END)

       

      where, CY MTD ,CY ROll 12 M ,CY MTD are boolean fields. till now my dashboard is working fine with this calculation.

       

      But, Now client has added one more KPI into list, which will be sum of 2 more KPI

       

      total value = value1+value2

      Value1 =IF ([Rec Type])="F" then (IS) end

      Value1 =IF ([Rec Type])<>"F" then (IS) end

       

      I need to show this total value on KPI selection: "IS" , as soon as i put this total value field in dynamic KPI calculation , it given me this error: "Cannot mix agg and non agg"

       

      I have tried using ATTR() function, but as soon as i apply attr it is giving me zero value.

       

       

      Can someone please help me with this.

       

      Sorry cannot attach workbook.

       

      Thanks,

      Sonali.