14 Replies Latest reply on Dec 10, 2018 2:22 PM by Joe Oppelt

    Walk around: Aggregate and Non Aggregate

    Daniel Ferreyra

      Hey Tableau Enthusiasts!

       

      I made the following calculated field to change according to the date

       

      Captura.PNG

      Nevertheless, when I try to use it in a further calculated field, I get the Error "Cannot mix aggregate and non-aggregate arguments with this function"

      Sin título.png

      So I ended up adding the "sum" function to get rid of the error, but I don't get the  value I'm looking for (300, 700, 360) instead I get the sum of those values.

      1.PNG

      I have tried  with the "attribute" function but the for the month of june i get an " * " as there are more than one value.

       

      How can I fix this error getting the (300, 700, 360) values?

       

      I really need help with it, I made the following image to be more precise:

      Presentación1.jpg

        • 1. Re: Walk around: Aggregate and Non Aggregate
          Joe Oppelt

          If you're getting an asterisk when you use ATTR, then you have multiple values of that calc for the [Month Number] you're looking at.  (Your Route KM calc is being done at the record-level.  So you could have different values of that calc within a given month.  And looking at the code for it, June 12 gives you 300, and June 13 gives you 700.  So for [Month Number] for June, you have multiple values in that calc.  Which value do you want to use in June?  (It looks like you are doing [Fixed Route KM] at the month level.)

          • 2. Re: Walk around: Aggregate and Non Aggregate
            Joe Oppelt

            PS:  It would really help to have a sample packaged workbook to look at instead of guessing how things are being used.

            • 3. Re: Walk around: Aggregate and Non Aggregate
              Daniel Ferreyra

              Hello Joe,

              Yeah I have multiple values, cause in that month we ran three different routes, so I need to use three of them. I just uploaded the file.

              • 4. Re: Walk around: Aggregate and Non Aggregate
                Ankit Bansal

                Daniel,

                 

                Can you try below calculation:

                 

                 

                Thanks,

                AB

                • 5. Re: Walk around: Aggregate and Non Aggregate
                  Daniel Ferreyra

                  Hey Ankit!

                   

                  It gives me no output for the month of june as there are three values

                  • 6. Re: Walk around: Aggregate and Non Aggregate
                    Joe Oppelt

                    Daniel Ferreyra wrote:

                     

                    Hello Joe,

                    Yeah I have multiple values, cause in that month we ran three different routes, so I need to use three of them. I just uploaded the file.

                    If you want three different values to happen, you have to have whatever differentiates those three values on the sheet.  Some "part of month" (maybe week?) in which those three values are distinct.

                     

                    I opened the workbook.  You have one mark, determined by month, for YES and for NO.  What do you expect to see there when there are three values?

                    • 7. Re: Walk around: Aggregate and Non Aggregate
                      Daniel Ferreyra

                      Hey Joe!

                       

                      The values are distinct upon date, i.e. we ran the 300km route from the 21 may to 12 june; we ran the 700 km from 13 june to 16 june and finally the 360 km from the 19  june to 29 june.  The whole purpose is to get  the number of kilometers he were supposed to run in a month on a fixed route, which I obtain with the product (no.days*Route km)=no km per month. I have done this sucessfully when we have just one route (i.e 800km), but now that  hcnages i have the aggregate not aggregate issue.

                      • 8. Re: Walk around: Aggregate and Non Aggregate
                        Joe Oppelt

                        I'm struggling to figure out what you need to do.

                         

                        I made a simple sheet (sheet 5) for all dates.  I displayed your [End of Month Accum] and [... Plan] on the sheet.  they are identical for all rows within a given month.  Maybe you want that.

                         

                        I also made a FIXED calc that FIXes youe [Route KM calc] for each row as appropriate for the date it falls on.  See [Route KM (copy].  Both the FIXED calc and the original [Route KM] calc are identical when displayed at the individual date level.  But I made my (copy) calc into a dimension.  You'll see its value as a dimension on the next sheet.

                         

                        On Sheet 5(2) I display the values at the month level.  Notice how your measures behave here.  (I displayed your Route KM as both a sum and an attribute.)  You can see the correct value as an attribute on this sheet.  But if you drag my (copy) calc off the sheet, you get your asterisk in June.


                        This is what I was referring to when I said you need some sort of dimension to compartmentalize the three values.  You have many rows that have 300 for the measure value, and many for 360, and many for 700.  So SUM will multiply your 300 value by as many rows as have 300.  Etc.  And at the month level, you have more-than-one different values of Route KM, so ATTR throws up its hands and tosses an asterisk.  But a dimension to differentiate the "chunks" of Route KM will let you identify the individual values, which is what I think you are needing here.  So is your goal to sum up the 3 values and use them in a calc?

                        • 9. Re: Walk around: Aggregate and Non Aggregate
                          Daniel Ferreyra

                          Hey Joe,

                          The following picture may clarify what I'm trying to do.

                          example.png

                          • 10. Re: Walk around: Aggregate and Non Aggregate
                            Joe Oppelt

                            You would put my FIXED calc as the color dimension there.

                            • 11. Re: Walk around: Aggregate and Non Aggregate
                              Joe Oppelt

                              And because you're doing that calendar at the day level rather than the month, you wouldn't be running into the asterisk problem.

                              • 12. Re: Walk around: Aggregate and Non Aggregate
                                Daniel Ferreyra

                                Hey Joe!

                                 

                                Could you further explain this solution please. I just checked how just what you did could adjust to what I'm looking for:

                                Captura.PNG

                                • 13. Re: Walk around: Aggregate and Non Aggregate
                                  Joe Oppelt

                                  In the attached I made a rudimentary calendar sheet on sheet 7.

                                   

                                  I used the FIXED calc as the color dimension.

                                   

                                  In sheet 7(2) I used your calc (set to DISCRETE) and it does the same thing.

                                   

                                  I am assuming that this is just test data.  You have missing circles because you have no rows on those dates.  I would guess that your actual data has a full set of dates within the data.

                                   

                                  If not, you'll need to look at a technique called "Data Scaffolding".  (Google "Tableau Data Scaffolding" to get a lot of discussions and how-to pages on that.)

                                  1 of 1 people found this helpful
                                  • 14. Re: Walk around: Aggregate and Non Aggregate
                                    Joe Oppelt

                                    As for the numbers you get in Sheet 5(2).  SUM(Route KM) is giving you the value times the number of actual rows you have in there.  So in May you have 8 rows.  (Not 10.)  8 x 300 is 2400.

                                     

                                    For 360 for June, you have 7 actual rows in there.  7 x 360 is 2520.

                                     

                                    I could use my calc, and month(date) and make another FIXED calc that finds the max and min for each Route KM(copy).  Figure the number of days in that range, and multiply that number by the KM (copy).  That would give you 11 days for May, not 10.  And you would therefore get 11 days for 360 for June (not 12) because your 360s go from then 19th through the 29th.  (Unless you want to build in assumptions that your data always fills to the end of the month.)