Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

I have a stacked bar graph for revenue by quarter, stacked by territory, but how do I add percentages to each territory??

I have tried everything trying to create this image and checked on the discussion boards and I am completely lost!!!

We have a sales dashboard for part of our company, it has $ value of revenue on the lefthand side, and each quarter along the bottom.

We have a stacked bar chart for each of the 5 territorys - AP, EU, GC, JP, NA

I need to be able to show the % amount of revenue by quarter for each region. i.e. if you look at the chart you could see for 3Q 2012, EU made up 25% of the revenue for the quarter, AP was 20%, GC was 20%, JP was 15%, and NA was 20%

See attached image of the dashboard as it is right now.

Our dashboard drills down on a certain BU, or end market segment, so the labels need to be able to flow if someone drills down for Buiness Unit A, the labels should change

5 Replies
JonnyPoole
Employee
Employee

A stacked bar + line isn't available in QLIK.  I have not seen an extension object for this one but i suspect its possible.

If you are looking for a 'quick fix' kluge you can try overlaying a transparent line chart over top a stacked bar chart to create the same effect.

It can be time consuming to create and maintain however.

combo stack and line.PNG.png

Not applicable
Author

Hi Jonathan thanks for your answer.

I'm not looking for a line, but looking more for this: see attached - i added the labels because i think i confused you 🙂

Where the % labels are on each peice of the stack that will total to 100% of the revenue for the quarter

JonnyPoole
Employee
Employee

Ok. On the presentation tab there is a 'plot values inside segments'  . If you enable that as well as 'values on data point' checkbox on the expression tab you'll see labels on each stack.

Then alter the expression to show percents and numbers using the dual() funciton:

if your expression for the numbers is sum(Sales) and you want the axes to show numbers and the labels to be % try this:

dual( num( sum(Sales)/   sum( total Sales), '#,##0%'),sum(Sales))

ps: to change it to a subtotal  ,  add the subtotalling field after total within <> symbols:

dual( num( sum(Sales)/   sum( total <subtotalfiedl> Sales), '#,##0%'),sum(Sales))

NickHoff
Specialist
Specialist

I had to do something like this and it's possible by using an if statement. 

I'd add the dimension year.  Then add an expression for each territory.

=if(Year='2004',SUM({$<Territory={AP}>}Revenue)),

  if(Year='2005',SUM({$<Territory={AP}>}Revenue)),

  if(Year='2006',SUM({$<Territory={AP}>}Revenue)),

ELSE statement

If your looking to get the %

Then

=if(Year='2004',SUM({$<Territory={AP}>}Revenue)/SUM({$<Territory={AP}>}TOTAL Revenue)),

  if(Year='2005',SUM({$<Territory={AP}>}Revenue)/SUM({$<Territory={AP}>}TOTAL Revenue)),

  if(Year='2006',SUM({$<Territory={AP}>}Revenue)/SUM({$<Territory={AP}>}TOTAL Revenue)),

your else statement could be a grouping of all of your years or anything else, if that value doesn't exist in your year dimension, i'd create an inline table for year 2004 through 2011 and a Combined YTD field.  Then use this instead of your Year dimension.

Now you could plot your line using the same logic with an if expression.  I'd be careful using an IF statement like this in an extremely large data set as QV isn't great at performing if statements.

Not applicable
Author

Jenna,

If all you want to do is add a %total inside the different stacks of each bar, try this:

  • Add a new expression that is the same as you are using for your actual stacked bar, i.e. sum(Sales)
  • In the Expression Tab
    • check Relative (to make your %total)
    • Under Display Options, uncheck "Bar" and check "Values on Data Points"

That should put the %total on each of the stacks.