business intelligence - Representing a nested stacked bar chart -

business intelligence - Representing a nested stacked bar chart -

i'm qlikview beginner , i'm working piece of data; trying visualize bar chart.

more specifically, need develop nested stacked bar chart shown in image. @ top level, every project, need have length of stacks of bar chart proportional "totalupdates" 5 different project locations. (loc 1 loc 5)

at sec level, within each 1 of stacks described above, need able represent percentage of completion. updatescompleted/totalupdates. (shaded or colored differently)

i tried using crosstable, did not work. since i'm trying work 3 dimensions, i'm unable find suitable solution handle this. snapshot of input spreadsheet , desired representation attached.

any help much appreciated. give thanks you!

your requirement challenging , have not perfect solution 2 approaches.

the first 1 simple chart 2 dimensions projecttype , projectsource.

the advantage of chart is simple , scales increasing projects , locations. there 2 formulas:

updates: = sum(updatescompleted) total: = sum(totalupdates)

but because of stacking of values changed to:

total: = sum(totalupdates)-sum(updatescompleted)

the sec 1 comes closer requirement:

but uses set analysis (see page 799 in reference pdf) define values of colums , have add together new coloumn when info contains new location.

the description first column (loc 1) is:

='loc 1 ' & round(sum({1<projectsource={'loc 1'}>}totalupdates)*100/sum(totalupdates)) & '%'

and definition is:

=sum({1<projectsource={'loc 1'} >}updatescompleted)/sum({1<projectsource={'loc 1'} >}totalupdates)

additionaly set backgroundcolurs first 3 columns

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

hope helps.

bar-chart business-intelligence qlikview