12 Replies Latest reply on Jun 22, 2019 7:20 PM by Mohammed Pareed

    Cannot mix aggregate and non-aggregate arguments with this function

    chandra vaddeman

      I have a filter with name called DATE_CREATED. I am looking for the max month (recent month) of selected values in the filter by using calculated field [Calculation1]

       

      [Calculation1]:   MAX(MONTH([DATE_CREATED]))

       

      I am using [Calculation1] calculated field in another  calculated field called [YTD_CALC]. Below is the code in [YTD_CALC]

       

      [YTD_CALC]:

       

      IIF (YEAR([PURCHASE_DATE]) = (YEAR(TODAY()))  and

      MONTH([PURCHASE_DATE]) <= [SalesRevenueMonthly].[Calculation1],

      [DAILY_SALES], 0)

       

      I am getting an error called "Cannot mix aggregate and non-aggregate arguments with this function".

       

      Any help on this is appreciated!

       

      Thanks!

        • 1. Re: Cannot mix aggregate and non-aggregate arguments with this function
          Ken Flerlage

          This is because Calculation1 is an aggregate but the rest of the fields you're using are not aggregates. Tableau can't mix these two types. That said, we can probably use a FIXED LOD to sort of trick it. If you change Calculation1 to the following, it will no longer act as an aggregate:

           

          {FIXED : MAX(MONTH([DATE_CREATED]))}

           

          You should then be able to use it in the formula.

           

          If you're not familiar with LODs, please be sure to read this: FIXED Level of Detail Expressions - Tableau

          1 of 1 people found this helpful
          • 2. Re: Cannot mix aggregate and non-aggregate arguments with this function
            Ken Flerlage

            Note: When using LODs, you need to be wary of the Tableau Order of Operations, especially when using dimension filters. So I'd suggest reading the following as well: Tableau's Order of Operations - Tableau

            1 of 1 people found this helpful
            • 3. Re: Cannot mix aggregate and non-aggregate arguments with this function
              Hari Ankem

              Try this:

              IIF (YEAR(ATTR([PURCHASE_DATE])) = (YEAR(TODAY()))  and

              MONTH(ATTR([PURCHASE_DATE])) <= [SalesRevenueMonthly].[Calculation1],

              SUM([DAILY_SALES]), 0)

              1 of 1 people found this helpful
              • 4. Re: Cannot mix aggregate and non-aggregate arguments with this function
                Mohammed Pareed

                Hi Chandra,

                As the error says, your [YTD_CALC], you are mixing aggregate and non-aggregate values.

                 

                IIF (YEAR([PURCHASE_DATE]) = (YEAR(TODAY()))  and

                MONTH([PURCHASE_DATE]) <= [SalesRevenueMonthly].[Calculation1],

                [DAILY_SALES], 0)

                 

                 

                Arguements bolded above are non-aggregates and  "[SalesRevenueMonthly].[Calculation1]" is aggregated because you are using MAX().

                You can fix this by aggregating all non-aggregates like below:

                IIF (ATTR(YEAR([PURCHASE_DATE])) = (MAX(YEAR(TODAY())))  and

                MAX(MONTH([PURCHASE_DATE])) <= [SalesRevenueMonthly].[Calculation1],

                SUM([DAILY_SALES]), 0)

                 

                If you see I have used a mix of aggregate functions just to convey the concept. You will have to use correct aggregates depending on the level of detail you have in your tableau view.

                Please go through this really helpful Tableau Page.

                https://kb.tableau.com/articles/issue/error-cannot-mix-aggregate-and-non-aggregate-arguments-with-this-function-when-creating-a-calculated-field

                 

                Option 1 is what I suggested above.

                Option 2 & 3 you can use if you change your user filter and let user select a single date or month.

                Based on what I understood from your question, You have a date filter through which User selects dates. Based on those dates selected, you find the recent month. Then you calculate the current year revenue till that recent month. Basically it is not Year till date, it is Year till month (recent month user selected). So, cant you simplify this by letting user select a single Month. Then when user selects the month, you just have one non-aggregated value and then your YEAR_CALC will be valid.

                Option 4 wont fit in this as you have dimensional filter. So LOD option wont be reliable as LOD is executed before your dimensional filter. Please refer https://onlinehelp.tableau.com/current/pro/desktop/en-gb/order_of_operations.htm for understanding this.

                 

                Hope this helps.

                1 of 1 people found this helpful
                • 5. Re: Cannot mix aggregate and non-aggregate arguments with this function
                  chandra vaddeman

                  Hi Ken, Thanks for quick response! However, I have used suggested approach but I am getting this error - "All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources."

                   

                  I have filter and calculation field [Calculation1] in different data source. Looks that's where the error is coming from. Not sure how to approach this.

                   

                  Thanks once again, for your time and help!

                  • 6. Re: Cannot mix aggregate and non-aggregate arguments with this function
                    chandra vaddeman

                    Hi Mohammed,  Thanks for your time. I can't use option 1, because I am aggregating YTD_CALC values on different dimensions after filtering records. So, I have this error - "The calculation 'Sum of YTD_CALC' can't be applied to a user-defined aggregate".

                     

                    I am using same filter for filtering data in other data source as multi select dropdown (users need multi select option).

                     

                    Thanks once again!

                    • 7. Re: Cannot mix aggregate and non-aggregate arguments with this function
                      Ken Flerlage

                      I think we'll need to see the actual workbook in order to determine the correct approach.

                      1 of 1 people found this helpful
                      • 8. Re: Cannot mix aggregate and non-aggregate arguments with this function
                        chandra vaddeman

                        Hi Ken,

                         

                        Please find attached work book, in my original post. I want to use YTD_CALC as SUM(YTD_CALC) under measures but  I am getting an error in YTD_CALC calculate field.

                         

                        Thanks!

                        • 9. Re: Cannot mix aggregate and non-aggregate arguments with this function
                          Hari Ankem

                          Does this look right?

                          1.png

                          1 of 1 people found this helpful
                          • 10. Re: Cannot mix aggregate and non-aggregate arguments with this function
                            chandra vaddeman

                            Hi Hari,

                             

                            Thanks for your time. I am getting an error - "cannot blend the secondary data source because unsupported aggregation ". I tried having both the calculated fields in the same data sources and using the filter values applied to the data set within the same source but it is no use.

                             

                            I still can't fathom why tableau doesn't have the solution for this case - For example  Max(1,3,5) as 5 and use the same 5 in if condition as mentioned below.

                             

                            IF YEAR(([DATE_CREATED])) = YEAR(TODAY()) and

                            MONTH(([DATE_CREATED])) <=  5

                            THEN ([M SALES])  END

                             

                            When I use some integer (like 5 ), I am getting desired results, but it doesn't work if I use {FIXED: (MAX(MONTH([DATE_CREATED]))) }.

                             

                            Thanks!

                            • 11. Re: Cannot mix aggregate and non-aggregate arguments with this function
                              Hari Ankem

                              I don't understand why you are getting an error, because I am not getting any error in the workbook that I have provided. If you are doing something else and/or trying out something new and then you are getting an error, you should provide that workbook and explain the issue. I can then see and understand what you are doing and suggest an appropriate solution. And one comment regarding the data blending you are doing, do you really have to do it? Can you not join the data sources and work with it?

                              1 of 1 people found this helpful
                              • 12. Re: Cannot mix aggregate and non-aggregate arguments with this function
                                Mohammed Pareed

                                Hi Chandra,

                                 

                                So You have blended data sources too. Interesting.

                                Its difficult to give a right answer unless you share the actual workbook. Coz in your real workbook you may have more issues.

                                But based on the sample workbook you have provided, few pointers and possible workaround at the end:

                                - I see you are doing a blend on Category/Group/Territory Name. But you have multiple purchase dates per Category/Group/Territory Name combination.

                                So using ATTR function to aggregate will fix the tableau error but your reports will be wrong as it will show blank for records when there are more than 1 purchase date.

                                Using ATTR in your solution is incorrect.

                                 

                                - I see you are still using FIXED calculation. It is not correct. Please read the links provided in my earlier replies.

                                 

                                - If both data sets are in same database, try doing a join.

                                And be careful when you join. In Primary source, you have multiple date created per Category/Group/Territory Name.

                                In secondary data source, you have multiple purchase dates per Category/Group/Territory Name.

                                if you just join on Category/Group/Territory Name you will see your metrics inflated. So be careful with your join.

                                 

                                - If they are in different databases and you cant join. Then understand try negotiiating with user and understand if there is a real need of multi selection. is it really required? If at the end you are deriving max (Month) based on his selections, Cant user just select the month till which he wants to see YTD? Then its opens up other options like using single select parameters, and then it becomes really easy.

                                 

                                - If none of the above works, then I will suggest a workaround.

                                In your secondary data source, do a cross join to months start dates of current year. pseudo code below:

                                Select S_DS.*, CALR.month_start_date

                                from

                                [secondary data source table] as S_DS

                                inner join

                                [calendar table] as CALR

                                on

                                s_DS.purchase_date<=CALR.month_start_date

                                and CALR.calendar date=CALR.month_start_date

                                and CALR.year=2019

                                 

                                I am assuming you have a calendar table with all dates.

                                second condition in above is to give one record per month.

                                 

                                Here you are duplicating your secondary source records with every month start date which is >= purchase date.

                                 

                                When you blend, you blend on Category/Group/Territory Name and also on MY(date  created) from Primary to Category/Group/Territory Name/MY(month start date) .

                                your YTD_CALC will be as below:

                                IF YEAR([Purchase Date]) = YEAR(TODAY())

                                THEN ([Daily Sales]) END

                                There is no need to check if the purchase date is less than or equal to max (month (date selected) as it is already handled by blend.

                                 

                                This will fix all the issues and give you right result. Only issue is volume increase in secondary data source as you are joining to every month start date of a year.

                                 

                                Please note the solution works on the sample workbook you provided. I am not across your real workbook. I hope you get the approach and you could use it your workbook.

                                I have attached a sample workbook for your reference.

                                 

                                All the best mate.

                                 

                                Regards,

                                Mohammed