Representing a nested stacked bar chart

Question

I'm a QlikView beginner and I'm working with a piece of data; trying to visualize it as a bar chart.

More specifically, I need to develop a nested stacked bar chart as shown in the image. At a top level, for every project, I need to have the length of the stacks of the bar chart proportional to "TotalUpdates" for the 5 different project locations. (Loc 1 to Loc 5)

At a second level, within each one of the stacks that I described above, I need to be able to represent the percentage of completion. Which is UpdatesCompleted/TotalUpdates. (Shaded or colored differently)

I tried using the crosstable, but it did not work. Since I'm trying to work with three dimensions, I'm unable to find a suitable solution to handle this. A snapshot of the input spreadsheet and the desired representation is attached.

Any help would be much appreciated. Thank You!

Answer 1

Your requirement is challenging and I have not the perfect solution for you but two approaches.

The first one is a simple chart with two the dimensions ProjectType and ProjectSource.

The advantage of this chart is that it is simple and scales with increasing projects and locations. There are only two formulas:

Updates: = sum(UpdatesCompleted)
Total:   = sum(TotalUpdates)

But because of the stacking of the values I changed it to:

Total:   = sum(TotalUpdates)-sum(UpdatesCompleted)

The second one comes closer to your requirement:

But it uses set analysis (see page 799 in the Reference PDF) to define the values of the colums and you have to add a new coloumn when your data contains a new location.

The description for the first column (Loc 1) is:

='Loc 1  ' & Round(sum({1<ProjectSource={'Loc 1'}>}TotalUpdates)*100/sum(TotalUpdates)) & '%'

And the definition is:

=sum({1<ProjectSource={'Loc 1'} >}UpdatesCompleted)/sum({1<ProjectSource={'Loc 1'} >}TotalUpdates)

Additionaly I set the backgroundcolurs for the first three columns

to visualise the progress (<0.5 red; orange; >0.8 green)

Hope that helps.