1 Reply Latest reply on Jun 7, 2013 4:18 AM by Jim Wahl

    How to sum amounts from distinct IDs

    Cesar Hernandez

      Hello everyone,

       

      This is the first time I post on this forum. Most of the times I find the answer to my questions on other people posts but this time I had to post.

       

      I need to sum amounts from distinct IIDs.

      detail.PNG.png

      There are two stores and each one of them have different ID Items with different colors and the same amount for the same item. I have a graph that shows the count of distinct IDs vs the amount, but the amount that is showing is the sum off all the amounts by ID and color and it should be only one amount per ID. In addition, I need to have a filter by color to compute this.

       

      Graph.PNG.png

      The summary should also change based on the filter.

      summary.PNG.png

       

      Thanks a lot for your help...

        • 1. Re: How to sum amounts from distinct IDs
          Jim Wahl

          Hi Cesar,

           

          This is confusing case of aggregations and table calcs and a reminder that Tableau generally only operates on data that is in the view.

           

          Your Distinct Items = COUNTD(ID) works when the level of detail in the view is the Store level. If you add Color or ID, the aggregate will be calculated at the finer granularity and the result will be the distinct number of IDs per color or ID, which of course is not what you want. On the other hand, to calculate the amount you need the IDs in the view.

           

          Table calcs allow you to aggregate at different levels of detail. I created two new table calcs:

          Distinct Items (table)

          IF FIRST()==0 THEN

               WINDOW_COUNT(COUNTD(ID))

          END

           

          Amount Sum per ID

          IF FIRST()==0 THEN

              WINDOW_SUM(AVG([Amount]))

          END

           

          The table calcs should be self-explanatory (ignore the if FIRST()==0 for a minute), but the tricky part with table calcs is the compute using.

           

          Duplicate your summary sheet and add the new Distinct Items (table) field to the Measure Values shelf. Most likely you'll see 9 as the count, instead of 5 and 4 for Boston and New York, respectively. This is because the compute using is set to Table Down---right-click on the pill and select compute using ID. Now the window that's being "addressed" is the ID level, and you'll be counting IDs for every other dimension---only store in this case.

           

          Note that the inner COUNTD() doesn't do much in this case because the window function is already operating at the ID level. You could replace this with ATTR(), MIN(), SUM(), ... with no difference.

           

          Next is the Amount Sum per ID. Again, after adding this, right-click and select compute using ID. If you remember, you can specify the default compute using by clicking on the blue text in the upper right corner of the edit formula dialog box. I usually forget, and it's not foolproof, as the compute using spec can change depending on what dimensions are in the view. Still, it's a good habit.

           

          Now duplicate your graph and replace the fields with the new fields---just drag the fields on top of the current pills. Again, you'll need to right-click and select compute using (this is where the defaults save time). You should now have the view you want.

           

          One more detail: IF FIRST() == 0 in the Amount calculation. If you comment this out, you'll (most likely) see that the graph marks get slightly darker and the labels disappear. If you select one of the marks you'll see "4 of 9 marks" in the lower right. What's happened is that you're now returning multiple values for this field. Click on the new Summary tab, and you'll see an entry in every row. If you uncomment the IF FIRST(), you'll see the calc returns a value for only the first row.

           

          You also need this trick to make the summary concise view. Move ID to level of detail and from the menu bar select Analysis > Stack Marks > Off.

           

          Lastly, filtering by color (or any other field) will work fine. The filters are applied at the data source and, therefore, the data available to the view and to the table calcs is the reduced set.

           

          Hope this didn't confuse you more. ...

           

          Jim