1 2 Previous Next 18 Replies Latest reply on Jun 27, 2016 4:15 PM by Jonathan Drummey Go to original post
• 15. Re: How to calculate sum of distinct values

Great comments - I hadn't thought about those differences between Table Calcs and LOD Calcs, but I have been thinking that Table Calculations make more sense to me in many cases -- I believe that is likely due to the visual aspects of building Table Calcs (as well as experience and practice, of course)

I definitely think there's a huge potential for creating/improving ways to teach these concepts to users. Please continue to share your opinions on how best to teach/learn aspects of Tableau -- I really believe a large portion of the Tableau user community would be lost without you, Jonathan.

• 16. Re: How to calculate sum of distinct values

Thanks a lot Jonathan,

LOD really did the trick for me. Trying to blend the different data sources is just too slow and for some calculations it just doesn't work.

I've found Tableau a neat tool to use so far. But when it comes to going through data with many-to-many relationships, there's a lot of pain for both designing the data-model outside Tableau, and bringing up the right calculations inside the dashboards.

In my situation, LOD does work alright - but it takes between 10-45s to refresh the views between selections. This is for data aggregated at each week, when the business would appreciate aggregation for each day.

Of course I'm looking for a better solution.

Did you come across good readings to tackle many-to-many relationships?

Thanks again!

• 17. Re: How to calculate sum of distinct values

Jonathan,

I'm coming into this late ... I had a need to do this, I did a search and up came your awesome solution.

I noticed you get the same answer when you sum:

{FIXED [Sales] : AVG([Sales])}

{FIXED [Sales] : MIN([Sales])}

{FIXED [Sales] : MAX([Sales])}

I'm still trying to get my arms around how this is working.  Is there a way to view the SQL that Tableau generates?

Thanks for the post!

Stuart

• 18. Re: How to calculate sum of distinct values

Hi Stuart,

{FIXED  : AVG()} is telling Tableau to compute the average value of Sales for each distinct value of Sales, which means the average and distinct values are the same. The same goes when you do {FIXED  : MIN()} (the min value of Sales for each distinct value of Sales) and MAX.

In other words the three FIXED calcs together could create SQL that looks something like this:

SELECT , AVG(), MIN(), MAX()

FROM

GROUP BY

And all three would return the same value.

The exact query that Tableau generates depends on the level of detail of the view and the dimension declaration in the LOD expression, as well as what filter(s) you have in play. So Tableau might be able to do everything in a single query, or it might calculate the LOD expression as a subquery that is inner-joined to the main query. In a view with no dimension pills and no filters (i.e. a calculation across the entire data set the sum of {FIXED  : AVG()} would be something like:

SELECT SUM(.[Distinct Sales])

FROM (

SELECT .[Sales]

, SUM(.[Sales]) AS

FROM

GROUP BY ) AS

You can view the SQL by looking at the Tableau logs normally stored in …\My Tableau Repository\Logs, you’ll want to look for lines that have “end-query” in them. Note that if you are using a Tableau data extract or an Excel or text file using the default connector you’ll be seeing Tableau’s internal query language, not SQL, and there’s no public documentation for Tableau’s query language. Make sure to press F5 to force a refresh of the view, if you’re just moving pills around Tableau may use the query cache for the view and not actually issue new query that would be logged.

Jonathan

1 of 1 people found this helpful
1 2 Previous Next