I’m trying to create a stacked-bar graph totaling 100%, with the dimension called “Final Approver” spread across the dimensions Year and Month. I got it working beautifully, except it is showing labels for Months in Years that have no data, thus creating big blank areas at the beginning and end of my chart. For example, my data starts 02NOV2012 and goes through today (16JUN2014). However, the bar chart shows the months Jan-Oct 2012 and Jul-Dec 2014, even though they have no data.
Here’s what else you may need to know. I created a Year and Month field out of the original Date with these LOAD statements:
MakeDate(Year(Approval_Exit__c), Month(Approval_Exit__c), Day(Approval_Exit__c)) as ApprovalExitDate,
Year(Approval_Exit__c) as ApprovalExitYear,
Month(Approval_Exit__c) as ApprovalExitMonth,
Day(Approval_Exit__c) as ApprovalExitDay,
My expression is:
count(DISTINCT(if(Approval_Status2 = 'Approved', DART_ID__c)))
/count(TOTAL <ApprovalExitYear, ApprovalExitMonth> DISTINCT(if(Approval_Status2 = 'Approved', DART_ID__c)))
My chart dimensions are:
Approval_Exit_Year
Approval_Exit_Month
Final Approver
I have done the following:
1. I have the “Suppress When Value is Null” box checked for all three dimensions.
2. When I create a pivot table with the same data/dimensions, there is NO value for the year/months in questions…it’s just when it’s a Bar Chart that these year/months show null.
3. If tried adding “Not(IsNull)” in the expression above and still not getting the results I expect.
Any thoughts?
