-
1. Re: How to retain 'null data' columns when a filter is applied?
Nipun Garg Feb 14, 2017 1:20 AM (in response to Laura Vecchio)Hi
This happen because your data set doesnt contain values of those month for that company
first make a parameter for company name then
You should have to create a field which state that if parameter = company then amt else 0 end
Hope it works
-
2. Re: How to retain 'null data' columns when a filter is applied?
Laura Vecchio Feb 14, 2017 2:10 AM (in response to Nipun Garg)Hi, Nipun!
Thank you very much for your advice, I definitely feel like it could work but it's still not quite right.
A few questions if I may?
a) does a parameter allow multiple value selection?
My parameter is set up as follows with the 'allowable list' added from the 'Company Name' field.b) It's now showing all columns which is brilliant but it's also displaying ALL data rows even those which don't relate to the selected parameter and it looks pretty chaotic;
c) I'm trying to force ZN's in the calculated field (so blank cells display as '0' and therefore colored red) however I'm struggling due to aggregation.
IF [Parameters].[Company Name] = [Company Name (System Control)]
THEN ZN(LOOKUP(1/[Exchange Rate]*[Placement Fee]),0)
ELSE 0
END
My original AMOUNT field before I added the parameter was calculated by;
ZN(LOOKUP(SUM(1/[Exchange Rate]*[Placement Fee]),0))
But this obviously doesn't work as part of an IF statement.
I'm beginning to think the report is impossible
Thank you for your help!
Laura -
3. Re: How to retain 'null data' columns when a filter is applied?
Nipun Garg Feb 14, 2017 2:41 AM (in response to Laura Vecchio)Hi
Currently Tableau not Support multi selection in parameter.
For another query : you can use filter for 0 values
-
4. Re: How to retain 'null data' columns when a filter is applied?
Nipun Garg Feb 14, 2017 2:49 AM (in response to Nipun Garg)please use
Sum(IF [Parameters].[Company Name] = [Company Name (System Control)]
THEN ZN(LOOKUP(1/[Exchange Rate]*[Placement Fee]),0)
ELSE 0
END)
-
5. Re: How to retain 'null data' columns when a filter is applied?
Laura Vecchio Feb 14, 2017 2:55 AM (in response to Nipun Garg)Thank you for your help, Nipun. Unfortunately however none of these solutions work!
I appreciate your time though
-
6. Re: How to retain 'null data' columns when a filter is applied?
Tharashasank Davuluru Feb 14, 2017 3:25 AM (in response to Laura Vecchio)1 of 1 people found this helpfulHi Laura,
Did you try this Go to Analysis >Table layout> Show columns with no data or Analysis>Table layout>Show rows with no data.
Thanks,
Tharashasank
-
7. Re: How to retain 'null data' columns when a filter is applied?
Tharashasank Davuluru Feb 14, 2017 3:30 AM (in response to Laura Vecchio)Hi laura,
Please go through this thread Re: Force Blanks to Show. and check the response of Mr.Jonathan Drummey hehas explained some workarounds.
if it did not answered your query please let us know.
Thanks,
Tharashasank
-
8. Re: How to retain 'null data' columns when a filter is applied?
Ben Riley Mar 29, 2017 8:33 AM (in response to Laura Vecchio)2 of 2 people found this helpfulHi Laura
All you need to fix this problem is a new calculated field and the power of 'context' filtering!
I had a problem where I needed to show only the latest data for various metrics for various organisations but some organisations had no data for certain metrics whereas others did. This was fine for each organisation, but when changing organisations using the filter I'd get a different list of metrics per organisation whereas the client required it to be the same list everytime whether there was data or not. In the end I tried 'Analysis->Table Layout->Show Empty Rows' but this showed everything. The solution was to create a new calculated field to filter things and 'add to context', this replaced my original field filter. So in my case my new calculated field was:-
IF [Is Latest Data] = 1 OR [Is Latest Data] = NULL THEN 1 ELSE 0 END
So it would return '1' if it was the latest data or empty, but crucially it would return 0 if there was data but it was not the latest data. In your case I guess it would be something similar, maybe not 'latest data' but rather 'appropriate dates' or whatever you're filtering for time period, but this should (hopefully) sort the problem.
Note that if there are any other fields bringing missing values back you'd need to apply the same logic to them and create a field for them, or filter them all in 1 big calculated field - just extend the above calc. This should be obvious as when you apply the first new filter you'll see if there's loads of other data still showing, just keep adding to this new field until you're filtering out what's not required.
Hope that helps