topic AW:How to make stacked-bar charts with each bar showing relative stacks. in QlikView

How to make stacked-bar charts with each bar showing relative stacks.

Thu, 10 Mar 2011 08:31:21 GMT

I have a requirement that seemed simple but once I start doing it..i got stuck [:(]

Let me display a rough mock up of whats required.

Here, my requirement is to display the 'new joinee count' relatively for each bar..with quarter stacked. That is, each year bar will have quarter stacks. Each year bar will show as 100% and its stacks show the relative % of new joinee count for each quarter.

I am using QlikView 9..and my source is a data warehouse. I have YEAR and QUARTER as seperate dimensions. In expression I am giving the sum of NEW_JOINEE_FLAG from the fact table.

In the 'expression' tab, we have an option to check relative...but as u might know..this relativity applies across the bar..that is, it'll show how much percent of the total count over two years does each quarter for each year has. So am totaly stuck here..I feel that there's no inbuilt function for this and there need to be some kind of set analysis expression solution for this.

Expecting guidance from the experts.

AW:How to make stacked-bar charts with each bar showing relative stacks.

brenner_martina — Thu, 10 Mar 2011 08:55:35 GMT

Hi sircute123,

your expression is something like:

Sum(Value)/Sum(Total <Quarter> Value)

AW:How to make stacked-bar charts with each bar showing relative stacks.

Thu, 10 Mar 2011 09:40:35 GMT

Hi Martina,

still some issues and it is not working as expected when I tried it the way you suggested.

I gave it like

Sum (NEW_JOINEE_FLAG) /sum(Total<QUARTER_ABBR> NEW_JOINEE_FLAG)

AW:How to make stacked-bar charts with each bar showing relative stacks.

Thu, 10 Mar 2011 10:05:10 GMT

I am just thinking the logic visulizing just one bar...

So one bar (one year) will show 100% with quarters as stacked, showing their individual contribution % to make it 100%.

so the expression should be like

(total new joinee count for a quarter (of a year) / total new joinee count of the very same year)

but am not proficient enough to convert it to qlikview set expression ..

AW:How to make stacked-bar charts with each bar showing relative stacks.

Thu, 10 Mar 2011 10:08:42 GMT

Try changing QUARTER_ABBR to YEAR in the above expression.

AW:How to make stacked-bar charts with each bar showing relative stacks.

Thu, 10 Mar 2011 10:29:35 GMT

Thanks iassen... it worked finally

(Sum (NEW_JOINEE_FLAG) /sum(Total<YEAR_NUM> NEW_JOINEE_FLAG))*100

and Martina..thanks for giving the initial hint

@ iassen : I am coming across this sum(Total<> value) kind of expression for the first time. It'd be great if you can let me know what it actually does.

If my understanding is correct, from this experience, I feel that the numerator specifies the general expression and denominator tells that its relative specific to each year... Am i right there?

AW:How to make stacked-bar charts with each bar showing relative stacks.

Thu, 10 Mar 2011 10:38:23 GMT

Sum (NEW_JOINEE_FLAG) - returns the summation of NEW_JOINEE_FLAG for each combination of dimensions that you have in your chart, and in this case that is for each year/quarter combination.

sum(Total<YEAR_NUM> NEW_JOINEE_FLAG) - returns the summation of NEW_JOINEE_FLAG only for the dimension(s) mentioned in the brackets and disregarding the rest of the dimensions that you might have in your chart, and in this case that is the year and the quarter is getting ignored, so for all quarters in the same year, you will get the same value.

HINT: You don't need the (*100) part. Instead, try checking the "Show in Percent (%)" setting for your expression in the properties of your chart, in tab Numbers.

AW:How to make stacked-bar charts with each bar showing relative stacks.

Thu, 10 Mar 2011 13:01:10 GMT

Thanks a lot iassen 🙂

that was very informative..

AW:How to make stacked-bar charts with each bar showing relative stacks.

Thu, 10 Mar 2011 13:27:25 GMT

You are welcome! 🙂

Good luck!