Hi, First time poster here who can't find anything specific to this issue on the boards. Found one close talking aboug a Gauge chart, but it didnt quite address this.
Please help!!
Background:
QV 9 document with 38 months (using month end files of data) User Dashboard has Year and Months (as created by the Master Calendar they teach in the classes) set to "Always Select One Value."
I have a simple bar chart that i'm using to display the year over year dollar amounts, dependant on what months and year the user selects. I.E. June 2008 - August 2011 is loaded in. If the user selects August 2011, the chart shows dollar amounts for all 4 Augusts in the application. If the user selects April, only April 2009-2011 appears as April 2009 was not loaded.
My primary chart expression (call it expression A) is as follows:
if(Year >= $(TopYear)-1, (sum({$ < Year = {$(=$(TopYear)-1)}, Month = {$(=GetFieldSelections(Month))}>} ExposureBalance) /1000000),Null())
The TopYear variable is a variable I created that returns the Max of the Year loaded (set to max all records, not just selected) So that I always will get the most recent year. This expression is used to create the 2nd total on the chart, the year just prior to the current or "Top" year. The other expressions either do not have the "-1" (i.e. current year) or have "-2".
My issue is with the highest promoted expression (call it expression B)) used to return the farthest month back. Now, as it is possible that the earliest year might not have that month in question, I use an if statement to return a Null so that the field will not display in the chart in that scenario, instead of as 0.
if((sum({$ < Year = {$(=$(TopYear)-3)}, Month = {$(=GetFieldSelections(Month))}>}ExposureBalance) /1000000) = 0, Null(), (sum({$ < Year = {$(=$(TopYear)-3)}, Month = {$(=GetFieldSelections(Month))}>} ExposureBalance) /1000000), )
The problem is, with the data I've outlined above, when the user is in 2010 or 2011, everything is fine. But when 2009 is selected, only those months that have data in them for 2008 will display in the chart. Those months where 2008 does not any values I get no data to display, even though expression A does have a value returned for 2009. I've put that expression A into a button and the proper value is shown. Yet the chart will not display it.
If I remove the check box on "enable" for that expression B, then suddenly expression A will display in the chart perfectly. But as soon as I re-enable the expression B, expression A will no longer display. Yet, as I said, the entire time expression A will return the proper value in a seperate button. What is more, if i take the Null() out of expression b and put in a value, say 5, then expression A will work again. but I want expression B to return Null when the user in the scenario I've outlined so that qlikview does not display it in the chart as 0, but rather does not does not display the expression at all.
I'm going mad with this, please help!!!!! 🙂
