1 2 Previous Next 28 Replies Latest reply on Jun 24, 2015 12:21 AM by Bora Beran

How to sum distinct Status

Hi everyone,

i have the Worksheet as the attachment. i want to count the number of each Status base on 2 Column: max Status and Min Status. i have calc like this:

if [count selection]="count max status" then SUM ([max Status])

elseif [count selection]="count min status" then SUM ([Min status])

end

however the result is not correct because there are many duplicate rows. the correct result is that:

when i select "Count max Status" then green:2 and red:1

when i select "Count min Status" then green:1 and red:2

Can you please help me how to do that?. Thanks

• 1. Re: How to sum distinct Status

Hi is there anybody can help me?. can we Sum distinct the column: Max Status?

• 2. Re: How to sum distinct Status

Hello Phuviet,

Since you are on 8.X, this was a little more difficult then I intended. In addition, the underlying data shows a lot of duplicate records, which made added to the complexity.

Either way, here is the attached workbook.

I will start putting together screen shots and explanations on how it did this, because it involved a lot of table partitioning.

Regards,

Rody

1 of 1 people found this helpful
• 3. Re: How to sum distinct Status

Ok first, we need to address the fact that you have you a lot of duplicate records, and you have you have a lot of fields in the view, which means all of those duplicate records are being brought into the partitions.

This means if we do SUM, or even WINDOW_SUM, it is going address all of those duplicate rows. We could normally handle this by tweaking what the Table Calc is Addressing and Computing on, but we have a lot of fields in the View, so this method won't work.

So to get around this we need to address the Status, and the First instance within the partition

This will give us only 1 instance for a particular status.

You have to duplicate this for each status.

Next we need to create Table Calc to build on top of it, and get the Total count for the Status

Once again, you need to duplicate this for each status

Then drag both of those Measures onto your Text Mark, and clean it up

Since we are only given a value for the status type, we can put these next to each other.

Finally we want to get rid of the excess columns. we can do this by creating a Calculated field

FIRST() = 0

Place that onto the Filter shelf and compute using status

I hope this is helpful.

Regards,

Rody

1 of 1 people found this helpful
• 4. Re: How to sum distinct Status

Simon Runc

Hey Simon, sorry for calling you in so much, but you are always extremely helpful!

The method I used works, but it is a complicated processes (Might have gone a little too far outside of that box).

The three big issues I had here was 1. There are duplicate records, and 2. There is a lot of fields in the View, and 3. It's not V9

Can you let me know if there is any easier way? His original workbook is attached in the first post.

Thanks,

Rody

1 of 1 people found this helpful
• 5. Re: How to sum distinct Status

hi Rody,

Not a problem...always happy to help.

I see what you mean, it does seem 'unexpectedly' complicated, for what, 'on the face of it' doesn't look like it would be so tricky.

I've had a quick play, and think that you do need to handle the 2 status' in different calculations, although I only tried a few other ways of concatenating some sort of unique string, and doing a window_sum(countd...) type thing, but couldn't get it to work.

However the solution you'e given here works, which is 99% of the battle! and I think the real answer might be to see if the data can be de-duplicated at source. I found, from bitter experience, that a bit of extra time spent on data-prep can save hours in the long run.

I'm away for a few days, but I'll take a further look when I get back (I love these kind of logical problems...they usually get the better of me, but I still enjoy them!!)...Like you I feel there is a simpler solution out there!

1 of 1 people found this helpful
• 6. Re: How to sum distinct Status

hi Rody,

...back from my hols! and I've had a further play with this and think I've found another (slightly simpler solution)

In this solution I've created calculated fields for all the elements for explanatory reasons, but could be combined into 1 or 2 in a final solution.

So the first thing I created was a measure selector, called 'Selected Status - Min or Max' with the formula

if [count selection]="count max status"  Then [max Status]

elseif [count selection]="count min status" then [Min status]

end

Then I created a field which creates a concatenation string of the class & project, where the selected status = 1, called 'Concat Selected Status'

IF [Selected Status - Min or Max] = 1 THEN [Class]+[Project] END

NB. Great Tip btw, as you'll see there is no ELSE statement here so the false of this logic statement is NULL, and NULLs don't get counted or COUNTD'ed (if that's a word!)

Now at this point, just putting status *Red/Green' on the row pane, and doing a COUNTD on this field gives the correct answer, but to get the correct answer with the extra dimensions in we need to put this into a Window_Sum Table Calc.

This field is CountD Window Sum, with the formula

WINDOW_SUM(COUNTD([Concat Selected Status]))

I then set the compute using as follows

One thing you'll also notice is I set the Min and Max status in the view to Attributes so I could ignore them in Table Calcs.

I think this does what we want, but you may want to double check.

What a deceptively tricky problem!! (although I must admit to quite enjoying it!! )

1 of 1 people found this helpful
• 7. Re: How to sum distinct Status

Version 9 (LOD expressions) would make this very simple, I believe!  Upgrade ASAP!

1 of 1 people found this helpful
• 8. Re: How to sum distinct Status

Simon this is great! Much easier (To understand and implement) than my original solution.

Using the concatenation of [Class] and [Project] was a really smart approach

The one thing I really liked, was using ATTR() for the min and max status. That really makes things easier, and more flexible, as we don't have to add a new calc for every possible status. Great work!

If you come across any of these "Tricky Problems", please ping me on it. I, like yourself, love working through theses puzzles. And am very, very reluctant to ever say, "This is not possible"!

Thanks again and best regards,

Rody

1 of 1 people found this helpful
• 9. Re: How to sum distinct Status

Matt Lutton

Hey Matt,

I think we would still run into a similar problem with V9, as there are duplicate records. But I am very interested to see what your approach would look like.

Of the top of my head, I think it would be possible by Excluding a lower level dimension, but not quite sure how that would look.

Regards,

Rody

1 of 1 people found this helpful
• 10. Re: How to sum distinct Status

Hi everyone,

i used Level of Detail Calculation and it work very good for me.

However i got another Problem. my Real Data has more than 46.000 records. before i use Level of Detail Calculation Tableau shows me Report very quickly. But when i create the Calculation to sum distinct Value and upload the Report on Server, Tableau works very slowly. it takes me alway 4-5 Minutes. Sometime Tableau cant Show me the Report and send a Error Meassage.

I wonder if the Level of Detail make Tableau slower?

• 11. Re: How to sum distinct Status

Phuviet -- can you upload you V9 workbook for Rudy to see?

Rudy, I have not studied this problem in depth at this time -- but I was imagining that LOD expressions would allow you to do this more readily since we can calculate at different levels of granularity, regardless of dimensions in the view (like taking the SUM of MAX values, or similar -- without resorting to Table Calculations).

I would need to look at the example a bit more closely and understand the expected results to show you how I'd approach it.  If time allows me to dig into this later, I will try to do so -- I have quite a bit going on right now!

• 12. Re: How to sum distinct Status

Thanks Pooja Gandhi for help me with this Calculation.

• 13. Re: How to sum distinct Status

Hi Phuviet,

Are you connecting directly to your data source or are you using an extract (.tde)? If you are using an extract you could try to 'optimize' the extract and then republish. Like Matt I've not studies LoD performance, but this might help.

• 14. Re: How to sum distinct Status

Hi Simon,

Ist live Connection. When i upload the Report on Server i muss make sure that the live Connection to Database is choosen.

1 2 Previous Next