- Dates, with fields Number, Date, IsCreatedOn, IsClosedOn (contains one entry for each day each item was open)
- MasterCalendar, with fields like Date and MonthYear (e.g. "June 2013")
I want to create a bar chart with the sum of priority 1 items for each month of the current year. There are very few such items in my database, currently there is only one in January 2013, while all other months since have none.
The calculated dimension is: '=IF(Year(Today(0)) = Year, MonthYear)'.
The expression is: 'Sum({$<Priority={1}>} IsCreatedOn)'.
'Show all values' (in Dimension tab) is enabled and 'Suppress Zero-values' (in Presenation tab) is disabled. No other options have been changed in the chart properties.
The result is a bar chart with only three dimension values: Jan 2013 (1), Feb 2013 (0), Mar 2013 (0), although my data includes items from April and May as well. When I change the dimension definition to only '=MonthYear', I see all months up until December 2013, but also all months from the previous years, which I don't want.
When putting in the formulas into a list box, it works fine:
MonthYear
Sum(...)
Year(Today())
Year
=IF(Year(Today(0)) = Year, MonthYear)
Jan 2013
1
2013
2013
Jan 2013
Feb 2013
0
2013
2013
Feb 2013
Mar 2013
0
2013
2013
Mar 2013
Apr 2013
0
2013
2013
Apr 2013
May 2013
0
2013
2013
May 2013
I tried some things and found that, even when I change the expression to '1 + Sum(...)' the bar chart still shows only January (2), February (1) and March (1).
Can anyone please suggest why I get this strange result? Or even better, is there some way to always show all twelve Months of the current year in the Dimension, even those in the future for which no records exist yet?
i went through your last para. for that you will get one check 'Supress Zero-Values' which is by default checked, you have to un-check that. Hope this helps.
I already did that, as written in the third paragraph 🙂 But even with that option disabled, it shows only some zero values (Feb and March) while others (April and May) are still seemingly suppressed.
Thanks but I'm not sure about that. As I've tried to show in the table in my original post, the expression does return a real 0 (integer) for all months with no prio 1 records, not NULL or NO value. And as I also pointed out, even when I added 1 to all sums, two months (Apr, May) that then have an expression value of 1 do not appear in the chart.
Furthermore, the issue goes away when I change the calculated dimension from =IF(Year(Today(0)) = Year, MonthYear) to just =MonthYear, so I guess it has more something to do with that, although I have no idea why.
I will try to create a sample application and upload it here later.
Thanks. Yes, this option is disabled, but switching it on or off does not change anything. Please also see the response I just gave to tresesco's post.
