% of total in stacked horizontal bar chart?

[Anonymous]

I've run into an issue converting a chart from another popular visualization tool into qlik,

The problem is essentially this: for a given department/division at a customer , I want to show the relative % composition of each dimension within certain metrics as a stacked horizontal bar chart like this:

I've recreated the data in qlik using conditional dimensions and set analysis e.g., for the WTD relative % calculation above, my statement looks like:

=IF ((DIVISION_NAME='X') AND (PROD_FMLY_DSC='B' OR PROD_FMLY_DSC='C' OR PROD_FMLY_DSC='D' OR PROD_FMLY_DSC='E' OR PROD_FMLY_DSC='F'),(Sum({<FISCAL_YEAR_AGE={0},CLNDR_544_WEEK_NBR={"=$(CWN)"},ORIG_SRC_SYS_CD -={"T"}>} (SALES_UNITS)))) / (Sum(TOTAL{<FISCAL_YEAR_AGE={0},CLNDR_544_WEEK_NBR={"=$(CWN)"},ORIG_SRC_SYS_CD -={"T"},DIVISION_NAME={"X"},PROD_FMLY_DSC={"L"}>} (SALES_UNITS)))

and can get the data in a pivot table close to what we have in the other tool:

but when I try to covert it to a stacked bar chart what I get is this:

Essentially,I need to pivot or transpose the axes to get the representation to match the other tool in the first screenshot: wondering if there is an easy way to do this in qlik in a stacked bar chart without something like creating a custom cross table from a resident load for example?

Any thoughts/ideas/inputs greatly appreciated. The ask looks simple enough so I think I may be missing something obvious....

[sunny_talwar]

I think first thing you need to do is promote your Product Family dimension. Right now it is the second dimension and it is not showing up on the axis as your original/required chart. To promote a dimension you can use this:

Secondly, if your current expression is giving you correct output in a pivot table, I would think it should give you a correct output in bar chart with the above change. But if it still doesn't you will need to make a slight change to your expression:

=IF ((DIVISION_NAME='X') AND (PROD_FMLY_DSC='B' OR PROD_FMLY_DSC='C' OR PROD_FMLY_DSC='D' OR PROD_FMLY_DSC='E' OR PROD_FMLY_DSC='F'),(Sum({<FISCAL_YEAR_AGE={0},CLNDR_544_WEEK_NBR={"=$(CWN)"},ORIG_SRC_SYS_CD -={"T"}>} (SALES_UNITS)))) / (Sum(TOTAL <ProductFamily> {<FISCAL_YEAR_AGE={0},CLNDR_544_WEEK_NBR={"=$(CWN)"},ORIG_SRC_SYS_CD -={"T"},DIVISION_NAME={"X"},PROD_FMLY_DSC={"L"}>} (SALES_UNITS)))

Worked on a small sample:

Script:

Table:

LOAD * Inline [

ProductFamily, Dim, Value

WTD, A, 12

WTD, B, 11

WTD, C, 43

WTD, D, 4

WTD, E, 30

WTD Forecast, A, 10

WTD Forecast, B, 11

WTD Forecast, C, 39

WTD Forecast, D, 5

WTD Forecast, E, 36

WTD PY, A, 13

WTD PY, B, 8

WTD PY, C, 33

WTD PY, D, 4

WTD PY, E, 42

];

Expression:

=Sum(Value)/Sum(TOTAL <ProductFamily> Value)

I hope this will help.

Best,

Sunny