1 2 Previous Next 15 Replies Latest reply on Mar 7, 2018 3:50 PM by Shinichiro Murakami

Help needed!!! - A dynamic dimension based on Count Distinct

edward.alexogiannopoulos Oct 13, 2016 7:15 AM

Hi everyone,

In my head this one was straight forward, however I have hit a brick wall pretty early on.

I'm hoping that someone here will be smarter than me and able to suggest a solution.

I have data at different levels in a hierarchy. These are like Departments, Business Units, Cost Centres:

I want to create a calculated field called Dir x which can be Dir 2, Dir 3 or Dir 4 depending on the filters applied.

in terms of simple logic, my original idea was something like:

If Total (countD ( [ Dir 2 ] ) >1 Then [Dir 2]

Elseif Total (countD ( [ Dir 3 ] ) >1 Then [Dir 3]

Else [Dir 4]

End

With this, if a Viz only had 1 bar at Dir 3 level, it would drill down automatically to Dir 4 - amazing, right?

My issue is that I can't combine Aggregations with "If" functions.

I feel like the solution is in LoD calcs but I haven't been able to find one.

If anyone can suggest a work around it would reduce my number of sheets by 66% so there is a lot at stake for here !!!!!

As always - I look forward to your ideas.

Many thanks,

Ed.

1. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Sherzodbek Ibragimov Oct 13, 2016 7:20 AM (in response to edward.alexogiannopoulos)

Ed,
What kind if error are you getting? Does it say "Cant mix aggregate with non aggregate functions ..." or something like this? Also, is it possible to post your workbook or mockup of your data so we can tell how to fix it please. Thanks
Like Show 0 Likes(0)

Actions
2. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Shinichiro Murakami Oct 13, 2016 7:30 AM (in response to edward.alexogiannopoulos)
This does not necessarily mean I'm smarter than you, but for sure, I love Tableau more than you.

You can achieve this with below formula.
[Header]
if {fixed [Dir-1]: sum([Number of Records])}=1 then [Dir-1]
elseif {fixed [Dir-2] :sum([Number of Records])} =1 then [Dir-2]
else [Dir-3] end

Thanks,
Shin

Combined Header_SM_9.2.twbx 19.3 KB
Like Show 0 Likes(0)

Actions
3. Re: Help needed!!! - A dynamic dimension based on Count Distinct

edward.alexogiannopoulos Oct 13, 2016 8:52 AM (in response to Shinichiro Murakami)

Hi Shin,

thanks for sending this through. I think we are very close to a solution, however -

Is there a way to make it so that only Dir 2 OR Dir 3 show at any one time? At the moment we have a mixture of both levels.

Kind Regards,

Ed.
Like Show 0 Likes(0)

Actions
4. Re: Help needed!!! - A dynamic dimension based on Count Distinct

edward.alexogiannopoulos Oct 13, 2016 8:55 AM (in response to Sherzodbek Ibragimov)

Hi Sherzodbek,

Shin's file works pretty well as a mock file if that helps.

"Cant mix aggregate with non aggregate functions ..." - that's the error I get, because its not possible to mix "if" functions with "countd"

The reason I want this is so that users who have different access rights can view the same report with it all ready "drilled" to their required level.

Many thanks,

Ed.
Like Show 0 Likes(0)

Actions
5. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Shinichiro Murakami Oct 13, 2016 11:41 AM (in response to edward.alexogiannopoulos)

Not sure I understand your request, but is this what yu said?

if {fixed [Dir-2] :sum([Number of Records])} =1 then [Dir-2]
else [Dir-3] end

Thanks,
Shin
Like Show 0 Likes(0)

Actions
6. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Luciano Vasconcelos Oct 13, 2016 12:40 PM (in response to Shinichiro Murakami)

Shin, he wants an automatic drill-down.

It's something like this:

If Countd([Dir-1])     = 1 then
    If Countd([Dir-2]) = 1 then [Dir-3]
    Else [Dir-2]
    End
   Else
    [Dir-2]
End

I was trying to adapt using fixed but it didn't work.
Like Show 0 Likes(0)

Actions
7. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Shinichiro Murakami Oct 13, 2016 1:31 PM (in response to Luciano Vasconcelos)

Still not 100% sure, but is this the requirement?
Could you tell me What do you mean by "drill down" if this still does not meet the expectation.

[Calculation1]
if {fixed [Dir-1]: sum([Number of Records])}=1 then [Dir-2]
elseif {fixed [Dir-2] :sum([Number of Records])} =1 then [Dir-2]
else[Dir-3] end

Thnaks,
Shin
Like Show 0 Likes(0)

Actions
8. Re: Help needed!!! - A dynamic dimension based on Count Distinct

edward.alexogiannopoulos Oct 14, 2016 1:25 AM (in response to Shinichiro Murakami)

Hi everyone,

Thanks for your time on this one.

I think Luciano has understood what I am trying to do, but it is probably only possible by incorporating FIXED.

Shin - let me explain here:

Imagine a supermarket business. In the Supermarket there is an overall manager, two assistant managers, and some category analysts for each product. They all want to look at a cost report, but they need to see it at different levels of granularity.

When the overall manager looks at the report all charts and graphs should show Food and Non Food. (Dir 1)

When the Assistant manager views the report Graphs should show the Dir 2 categories for their area (Food or Non Food). This means the Food manager should see Bakery, Fish, Meat, and Sweets.

The category analysts are responsible for 1 Dir 2 level category each. This means when they login they should see all graphs at their Dir 3 level of detail. For instance a Category analyst for Books should see charts split by Fiction and non Fiction.

all of this will be driven by filters driven by their login and password.

So to summarise. If the total(countd) of a level is 1, I want to show the next lower level.

If there is only 1 distinct Dir 1 (e.g. Food) then show Dir 2 (Meat, Fish, Bakery, Sweets).
If there is only 1 deistinct Dir 2 (e.g. Fish), then show Dir 3 (Fresh, Frozen).

I hope this helps.

Many thanks,

Ed.
Like Show 0 Likes(0)

Actions
9. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Shinichiro Murakami Oct 14, 2016 8:47 AM (in response to edward.alexogiannopoulos)
Still not sure I understood the request, let me know if this still does not help.

[Header]
if
{fixed:countd([Dir 1])}=1 and {fixed:countd([Dir 2])}<>1
then [Dir 2]
elseif
{fixed:countd([Dir 2])}=1
then [Dir 3]
else [Dir 1]
end

Change all he filter to "Context"

Thanks,
Shin

Dynamic Header change_SM_9.2.twbx 19.5 KB
Like Show 0 Likes(0)

Actions
10. Re: Help needed!!! - A dynamic dimension based on Count Distinct

edward.alexogiannopoulos Oct 14, 2016 9:27 AM (in response to edward.alexogiannopoulos)

Shin,

I can not thank you enough for this!!

I owe you a coffee.

I am really excited by this - I think what we have here is something that can be applied to a lot of situations.

Have a great weekend.

Ed.
Like Show 0 Likes(0)

Actions
11. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Shinichiro Murakami Oct 14, 2016 10:19 AM (in response to edward.alexogiannopoulos)

Happy to hear that, but please remember,

LOD sometimes causes the slow response because of it's feature.
If you have tons of data set, it's not always attractive from performance standpoint.
As long as you use extract, I think that's still OK.

Thanks,
Shin
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
12. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Alisa Dubreuil Mar 7, 2018 2:03 PM (in response to edward.alexogiannopoulos)

Shin - Thank you very much for this! I've been looking to solve this for some time.
Like Show 0 Likes(0)

Actions
13. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Shinichiro Murakami Mar 7, 2018 2:10 PM (in response to Alisa Dubreuil)

HI Alisa, Alisa Dubreuil

This is one of very tricky approach which I wanted to remember , but I could not find this post easily by myself recently.
I'm just curious how you reached here?
I am asking search word or path which brought you here.

Regards,
Shin,
Like Show 0 Likes(0)

Actions
14. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Alisa Dubreuil Mar 7, 2018 2:19 PM (in response to Shinichiro Murakami)

My Google Search: TABLEAU DYNAMIC DIMENSION FROM LOD Again, this is smart stuff. Thank you.
1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions