11 Replies Latest reply on Feb 25, 2018 8:58 AM by Artem Brauner

# Calculation of max within dimension

Not sure what is the proper way how to search the problem I have.

I have the list clients and each client has a list of features it's using e.g.:

Client1, Feature1

Client1, Feature2

Client1, Feature3

Client2, Feature2

Client3, Feature2

Client3, Feature4

Each feature has it's own milestone e.g.:

Feature1, Milestone1

Feature2, Milestone1

Feature3, Milestone2

Feature4, Milestone3

After joining I would have something like:

Client1, Feature1, Milestone1

Client1, Feature2, Milestone1

Client1, Feature3, Milestone2

Client2, Feature2, Milestone1

Client3, Feature2, Milestone1

Client3, Feature4, Milestone3

I am trying to build something that will show me how many clients each milestone will have.

Milestone1, 1   (Client2 should go here as he has only 1 feature max milestone 1)

Milestone2, 1   (Client1 should go here as he has only 3 feature max milestone 2)

Milestone3, 1   (Client3 should go here as he has only 2 feature max milestone 3)

• ###### 1. Re: Calculation of max within dimension

Hi, Artem

Can you give me a bit more information about why only client 2 has been counted for milestone 1? It seems there are 3 clients with your sample data

ZZ

• ###### 2. Re: Calculation of max within dimension

To find out if Client belong to a particular milestone I need to find MAX underlying feature milestone.

I was think about LOD Expressions, but now it looks that some king of data blending should be applied, but I don't know how to write expression like if milestone = max(milestone) by client then 1 else 0

• ###### 3. Re: Calculation of max within dimension

Hi, Artem

Hope this helps

ZZ

• ###### 4. Re: Calculation of max within dimension

Thank you very much for reply.

I have extended original data set to show where the issue is:

Client1, Feature1

Client1, Feature2

Client1, Feature3

Client2, Feature2

Client3, Feature2

Client3, Feature4

Client4, Feature4

Client4, Feature5

And feature list:

Each feature has it's own milestone e.g.:

Feature1, Milestone1

Feature2, Milestone1

Feature3, Milestone2

Feature4, Milestone3

Feature5, Milestone3

So for the same client in single milestone there would be 2 features. As my original task was to find out number of client per milestone I made the following calculation as you suggested:

Filter_Bool as: [Milestone] = {FIXED [Client]:MAX([Milestone])}

Filter_Number as: if [Filter_Bool] == true then 1 else 0 END

When I am putting Milestone to Rows and Filter_Number to Measures which transform to SUM(Filter_Number) I am having the following:

Milestone

-----------------------

Milestone1     1

Milestone2     1

Milestone3     3     (here instead of 2 I see 3 as it's sums all underlying rows. For Client3 it takes Feature4 and for Client4 it takes both Feature4 & 5)

Does anyone know how to count sum by distinct in this case?

• ###### 5. Re: Calculation of max within dimension

Finally I was able to calculate what I was looking for using the following steps:

1. Create calculation field to find out Max milestone per client using: [Milestone] = {fixed [Client]:MAX([Milestone])}

2. Create another calculation field to find out max feature per client using: {fixed [Milestone],[Client]:MAX([Unique_Feature])}          I am not sure how it works and if it's right approach or I should do smth like ({FIXED [userid] : MAX(IF [Created Time] != [Max Created Time] THEN [Created Time] END)})

3. And finally create calculated measure as: if [Unique_Feature] = [Max_Feature_per_Client] and [Max_Milestone_per_Client_Bool] = TRUE then 1 ELSE 0 END

I am not sure if I did this type of task right, will do more testing, but it's very weird because if I just put Client to Columns and Milestone to Rows Tableau by default shows me the following:

Milestone          Client1     Client2     Client3     Client4

-------------------------------------------------------------------

Milestone1             Abc         Abc           Abc

Milestone2             Abc

Milestone3                                             Abc          Abc

And it looks not trivial to calculate numbers which are marked as bold per milestone

• ###### 6. Re: Calculation of max within dimension

Hi, Artem

Can you provide the workbook your working on?

ZZ

• ###### 7. Re: Calculation of max within dimension

Here you go.

Is there any way to do this in better way?

• ###### 8. Re: Calculation of max within dimension

can you attach workbook with file extension .twbx?

ZZ

• ###### 10. Re: Calculation of max within dimension

Hi, Artem

Is this what you want?

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 11. Re: Calculation of max within dimension

Thank you,

That's 2 steps less than my approach, I would assume this is the easiest way to do this as for now.