-
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
-
2. Re: Help needed!!! - A dynamic dimension based on Count Distinct
Shinichiro MurakamiOct 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
-
-
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.
-
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.
-
5. Re: Help needed!!! - A dynamic dimension based on Count Distinct
Shinichiro MurakamiOct 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
-
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.
-
7. Re: Help needed!!! - A dynamic dimension based on Count Distinct
Shinichiro MurakamiOct 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
-
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.
-
9. Re: Help needed!!! - A dynamic dimension based on Count Distinct
Shinichiro MurakamiOct 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
-
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.
-
11. Re: Help needed!!! - A dynamic dimension based on Count Distinct
Shinichiro MurakamiOct 14, 2016 10:19 AM (in response to edward.alexogiannopoulos)
1 of 1 people found this helpfulHappy 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
-
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.
-
13. Re: Help needed!!! - A dynamic dimension based on Count Distinct
Shinichiro MurakamiMar 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,
-
14. Re: Help needed!!! - A dynamic dimension based on Count Distinct
Alisa Dubreuil Mar 7, 2018 2:19 PM (in response to Shinichiro Murakami)1 of 1 people found this helpfulMy Google Search: TABLEAU DYNAMIC DIMENSION FROM LOD Again, this is smart stuff. Thank you.