Display 100% data points for missing data on a line chart?

[#1]

Hi everybody - first time poster here. I'm very new to QlikView, so go easy on me .

I believe this question has been asked many times, however I think my particular situation is potentially a little different and just wanted the experts input.

I have a simple line chart that displays the percentage of 'cases' that met our service level objective (SLO) for each month: MonthYear as the dimension, with the following expression: Sum(SLOFlag) / Sum(CaseCount). Simple.

A request came in asking to display 100% for a month if there were no cases, instead of a straight line from one month to the next datapoint. As far as I can understand, this is not nearly as straight forward as one would think.

I'm trying to wrap my head around all of the different methods I keep reading (create dummy values, use a fancy IF statement that ties to an isolated calendar separate from your data model, etc.) I just want to be guided in the right direction.

I am trying to avoid dummy values, since I think moving forward this could potentially make the app explode in size.

I am currently trying to utilize the isolated calendar method (separated from the current data model), but can't seem to get it to work correctly. Could something similar to the following expression potentially work?

Using IsoMonthYear as the dimension.

IF

     (

     IsoMonthYear = MonthYear,

          Sum(SLOFlag) / Sum(CaseCount),

     1

     )

Or is it much more complicated than this?

Thanks!

Paul

[#2]

Hi Paul,

maybe you can attach sample application to show your current data model and expressions?

regards

Darek

[#3]

Here's a quick mock up. As you can see, Company1 doesn't have any data for February. We would want this to be reflected as 100%.

In the second chart, it always shows 100% unless I drill down on a single month.

Thanks!

Paul

[#4]

I've found a workaround (which, of course, introduced a new problem).

I found that if I enable the 'Show All Values' option on the Dimensions tab within the chart properties, I am able to write an IF statement to display a 100% data point if no values exist.

Selecting company B now shows 100% SLO for Month 3 - even when there is no data for Company B in Month 3 (which is exactly what I want).

The problem with this method is that I can no longer restrict what months are shown on the graph. Since the 'Show All Values' option was checked, it will constantly show every possible month on the chart. It also shows all of the 'unselected' months as 100% since my IF statement doesn't see any data for those months (since they are being filtered out after being selected).

I was hoping there was a way to dynamically limit the amount of Months shown on the chart WITH the 'Show All Values' option checked.

I have attached a newer version of my Test App to better explain everything.

Thanks everybody!

[#5]

Hi there,

Attached is how I would approach this. Create a Master Calendar that would fill up all the missing dates between the very first and very last dates in your date field.

Please check out the back-end script and the chart expression on the front-end.

Hope it helps

Thanks

AJ

[#6]

Thanks for the fast reply Ajay! The problem with this solution is that when you select Company 1, you ONLY see values relevant to Company 1 (2013-Jan, 2013-Feb, 2013-Aug, 2014-Feb, 2014-May).

I need to be able to see ALL months when drilling down on a company, even if they have no data within those months. For the months that are missing data, I need the line chart to show 100%.

I have a second sample app attached in a previous response that does this, however now it won't let me drill down on a particular month.

Thanks again!

Paul

[#7]

Ah I see your problem. Simple solution that I can think of is create another expression which will identify when there is no data associated with the selections made.(Kinda like a flag).

I have used X mark. So even though the values show for all possible months, if there is an X mark then it would indicate thats not relevant to the selctions.

Would this work?

[#8]

It's pretty clever, however I unfortunately don't think that end users would like it. I just wish there was a way to bypass the 'show all values' and limit the months shown to what was selected.

Is there a way to somehow create a calendar that is generated on the fly? Maybe base the dates in the calendar off of variables that are set by user selections?

Thanks,

Paul

[#9]

Ok, If I have been following you correctly the 'Show all' seems to be an issue when you drill down(make a selection in the month field). In those cases you just want to see the selected months, correct.

Then may be this might help. I just created two charts and control them using conditional show.

Thanks

AJ