9 Replies Latest reply on Aug 23, 2016 6:01 PM by Andrew Watson

# Is it possible to create a classification (dimension) based on a measure?

In short - I want to create a dimension that is ClientStatus with three options: "New Client", "Reoccurring Client", & "Lost Client."

I have a Year and Revenue field that I would like to classify each client by.  Essentially in pseudo code...

If Client's revenue = 0 in 2015 and revenue > 0 in 2016 THEN "New Client"

Else If Client's revenue > 0 in 2015 AND 2016 THEN "Reoccurring Client"

ELSE IF Client revenue > 2015 AND revenue = 0 in 2016 then "Lost Client"

However no matter how I write this calculation I cannot for the life of me get it to work.  I want to be able to use this dimension in a bunch of visuals to filter on only new clients or clients we lost to identify trends.

Any advice on how to proceed with this?

Thank you!!

• ###### 1. Re: Is it possible to create a classification (dimension) based on a measure?

Hi.

I suppose you have a date field so i'll call it [Revenue date]:

If  Sum(If Year([Revenue date]) = 2015 then revenue End) = 0 and

Sum(If Year([Revenue date]) = 2016 then revenue End) > 0

Then "New Client"

ElseIf  Sum(If Year([Revenue date]) = 2015 then revenue End) > 0 and

Sum(If Year([Revenue date]) = 2016 then revenue End) > 0

Then "Reoccurring Client"

ElseIf  Sum(If Year([Revenue date]) = 2015 then revenue End) > 0 and

Sum(If Year([Revenue date]) = 2016 then revenue End) = 0

Then "Lost Client"

End

It's something like that.

Maybe you have a little syntax error but it's almost there.

1 of 1 people found this helpful
• ###### 2. Re: Is it possible to create a classification (dimension) based on a measure?

Perhaps a good place to start would be to create the revenue for 2015 and the revenue for 2016 in separate calculated fields. For example (you will need to adjust to match your field names) you might have:

Rev2015: IF YEAR([Date]) = 2015 THEN [Revenue] END

Rev2016: IF YEAR([Date]) = 2016 THEN [Revenue] END

Your classification formula would be something like:

IF SUM([Rev2015]) = 0 AND SUM([Rev2016]) > 0 THEN 'New'

ELSEIF SUM([Rev2015]) > 0 AND SUM([Rev2016]) > 0 THEN 'Recurring'

ELSEIF SUM([Rev2015]) > 0 AND SUM([Rev2016]) = 0 THEN 'Lost'

ELSE 'Other'

END

1 of 1 people found this helpful
• ###### 3. Re: Is it possible to create a classification (dimension) based on a measure?

Luciano must have posted his reply as I was writing mine. It's more or less the same as my suggestion.

1 of 1 people found this helpful
• ###### 4. Re: Is it possible to create a classification (dimension) based on a measure?

It happens all the time.

• ###### 5. Re: Is it possible to create a classification (dimension) based on a measure?

Thanks a bunch Andrew & Luciano.  I've gone and attempted your suggestion and I feel like I am real close however still some issues.  I created a field for 2015 revenue and 2016 revenue separately and went with your solution:

IF SUM([2015REV]) = 0 AND  SUM([2016REV]) > 0 THEN 'New'

ELSEIF SUM([2015REV]) > 0 AND SUM([2016REV]) > 0 THEN 'Recurring'

ELSEIF SUM([2015REV]) > 0 AND SUM([2016REV]) = 0 THEN 'Lost'

ELSE 'Other'

END

However when I drag it onto the view I only see 'Other' for all clients.  The calculated field is a measure (data type String)...is that the issue?  Does it need to be a dimension?  I tried dragging it over but it did nothing - perhaps because its an aggregation??

Thanks again

• ###### 6. Re: Is it possible to create a classification (dimension) based on a measure?

Please attached a packaged workbook or show a screenshot of your entire screen.

• ###### 7. Re: Is it possible to create a classification (dimension) based on a measure?

I used the following Calc fields:

2016D = if [Year] = 2016 THEN [Revenue] ELSE 0 END

2015D = IF  [Year] = 2015 THEN [Revenue] ELSE 0 END

Delta=IF [2015D] = 0 AND [2016D] > 0 THEN 'New' ELSE null end

I will add the recurring + lost later but just as a test I started with the 'New'(s) .  With this formula I get "New" for the 2016 revenue and Null for the rest.  What I really wanted (and I probably messed something up here)...is for this account to be classified as simply New because 2016 had revenue.  What Delta is currently doing is just classifying the 2016 revenue with a new marker so its no different than if i just filtered everything to 2016.  Does that make any sense?  thx

• ###### 8. Re: Is it possible to create a classification (dimension) based on a measure?

Hi Josh,

Looks like you may want to create a Dimension out of an aggregate.

This is possible in Tableau 9+ using FIXED LOD expression.

Please find the attached as an example (Superstore dataset).

Hope it could help.

Yours,

Yuri

• ###### 9. Re: Is it possible to create a classification (dimension) based on a measure?

Yuri could be right, FIXED may do what you need it to do. Without seeing your packaged workbook, or at least a full screenshot of your different pills in the view we're making stabs in the dark to a large degree. Your Delta formula shouldn't actually work at all as you're not summing the 2015D and 2016D fields.

Options you can try:

1. Change the Delta formula: IF SUM([2015D]) = 0 AND SUM([2016D]) > 0 THEN 'New' ELSE null end

2. LoD formulas:

2016D = {FIXED [Client]:SUM(IF [Year] = 2016 THEN [Revenue] END)}

2015D = {FIXED [Client]:SUM(IF [Year] = 2015 THEN [Revenue] END)}

Delta=IF SUM([2015D]) = 0 AND SUM([2016D]) > 0 THEN 'New' end

1 of 1 people found this helpful