Advanced bar chart offsetting

[Gavin]

Hello dear Qlikview friend,

It's been a few months now since  I've dove deep down into Qlikview, for this is the only Data Visualization tool available in our organisation. I can feel Qlikview is very powerfull, but I'm still very far away from truely understanding every possibility.

I want to thank the community so far, as the forum has proven to be a huge help in improving my KPI.

Here stands the issue currently faced, which has already kept me trying stuff for a few days.

The data consists of deliverables and dues dates.

Based on a formula, and the due dates being met or not, a custom category is created during the load, "Statut_ADH" (for adherence status). This category is the first dimension of our graph. The second dimension is the "Projet" (for project).
The expression counts how many delivrables enters each category based of the current month ( Sum(If(Due_Month or Done_Month = This month, 1)).

What we had in Excel was the total number of deliverables onto the left side, and various offset bar to the right one, the first bar meeting the left bar (target/goal).
With my poor Qlikview knowledge, i haven't been able to reproduce such a layout with the details of the second dimension, but it turns out the total was unecessary, since the bar offset of the first bar allow to visualize where stands the total. Here is what it should look like in the end (offset only on the first 4 bars) :

I have been able to play with the offset of the bars, however, there are two issues faced :

1/I need to apply the bar offset only to the first displayed project (For example, Atlanta), otherwise the offset is applied between each bar. I can do this manually (if (project="Atlanta", Offset), however, since I hide null values, if on a specific month, no deliverables are due for the project, there will be no bar offset at all. I believe I need a formula to determine the first displayed project.

2/ I had a first prototype with only one dimension (no project) where I managed to set the bar offset right (Cat 1 Offset : Count(<1 Statut_ADH={'Cat2','Cat3','Cat4','Cat5'}> Deliverable), (Cat 2 Offset : Count(<1 Statut_ADH={'Cat3','Cat4','Cat5'}> Deliverable), etc...). I can't find a way to count all the deliverables in a specific category with the second dimension, so each bar can be offsetted correctly.

I hope I've exposed my issues clearly enough, please feel free to ask any more details if needed, or to criticize my approach and suggest new ones if the opportunity shall arise.

Many thanks to everyone who will read this message, I appreciate your time.

Have a great day !
Gavin

[Brett_Bleess]

Gavin, given the nature of this one, likely going to need some sort of sample app so folks can look things over in detail regarding the data model and the chart expressions and settings in order to potentially come up with ideas for you.  I suspect this is why you have not received any comments thus far.  

Just an FYI, if you have confidential data in the app, you can use the Settings\Document Properties\Scrambling tab to scramble those fields, or generate a sample app with sample data that shows the issue is the other way to go, but I suspect if you can attach something, that should help folks leave you some comments on what may work etc.

Regards,
Brett