
1. Re: % calculated field help...?
G Marc Turner Jun 16, 2016 5:51 PM (in response to Steve Gesuale)1 of 1 people found this helpfulHi Steve,
Here are two possible options. The first option would to be to place [11 N] and [11 D] on the filters shelf. Use "All Values" and then make sure the box in the bottom right to "Include Null Values" is unchecked. This should fix all of the calculations. This would remove the countries that don't have both N and D from the table though.
The second option is to use IF statements in the formula to determine if the values are present
For #1, you could use this formula:
sum(iif(isnull([11 D]) , NULL, [11 N])) / sum(iif(isnull([11 N]) , NULL, [11 D]))
Basically, it adds an IF statement to determine whether the value should be included in the calculation. This won't impact the totals you get for [11 N] and [11 D] though since the values still exist when summed to get the grand total. You would need to create a calculated field for each.
For #2, you could use this formula which also is based on using an IF statement to determine whether both [11 N] and [11 D] are present before counting the record.
iif(isnull([11 D]) or isnull([11 N]), 0, 1)
Using the first approach based on filtering out records with NULL values is probably the easier approach since it removes the records from all the calculations so you don't have to miss something along the way. But, if you need to list all the countries, then the formula approach might be a better option.
Hope this helps
Marc

2. Re: % calculated field help...?
Ashish Chaudhari Jun 16, 2016 10:29 PM (in response to Steve Gesuale)1 of 1 people found this helpfulHi Steve,
I have created simple calculation field and added it to the filter and selected in "True". Please refer to the screenshot for the results.
Here is the code for the same.
[11 D]>0 and [11 N]>0
Output on sheet1
Output on sheet2
On Dashboard
I hope this helps. Unable to upload the file due to net problem.
Thanks and Regards,
Ashish Chaudhari

3. Re: % calculated field help...?
Ashish Chaudhari Jun 16, 2016 11:38 PM (in response to Ashish Chaudhari)Hi Steve,
Please find the attached for the same.
Ashish

% issues.twbx 28.8 KB


4. Re: % calculated field help...?
Steve Gesuale Jun 20, 2016 1:15 PM (in response to Ashish Chaudhari)Hi Marc and Ashish,
Thanks for your responses and explanations  very helpful! Really appreciate the clarifications.
I've run into a bit of a snag when going back to the raw data. It's more granular with a Date field that I suspect is causing problems. I tried an LOD calc based on the one Ashish added (in the attached workbook), but it doesn't work. I should have used the raw data to begin with...
Any continued advice would be great!
Thanks.
Steve
% issues 2.twbx 80.8 KB


5. Re: % calculated field help...?
Ashish Chaudhari Jun 20, 2016 11:00 PM (in response to Steve Gesuale)1 of 1 people found this helpfulHi Steve Gesuale,
Updated calculations for Calc_Filter. Put this in the filter section and select True. Earlier calculation was at each row (disaggregated).
SUM([11 _D])>0 and SUM([11 _N])>0
This will give you desired result.
Thanks and Regards,
Ashish Chaudhari

6. Re: % calculated field help...?
Steve Gesuale Jun 21, 2016 12:05 PM (in response to Ashish Chaudhari)Thanks again, Ashish! That does it and I understand the difference so learned something from this. Appreciate the help.
Steve 
7. Re: % calculated field help...?
Ashish Chaudhari Jun 21, 2016 11:24 PM (in response to Steve Gesuale)You are welcome Steve.