topic Re: Bar Chart visualization in QlikView

Bar Chart visualization

nagaiank — Wed, 03 Jun 2015 09:08:11 GMT

I have the following problem and need guidance and help to resolve it.

A company processes orders from several customers from the four regions. Each order has an order amount and order duration (time to fulfill the order). The summary data to be used for visualization extracted from the order detail table is given below. Using the application attached, I could get the stacked bar chart given below on the left, while the visualization needed is shown on the right. Please advise the changes in the application to get the desired visualization. You may suggest any other chart object which will display all the information (order amount and order count for each order duration, and the total order amount and total order count for each region). Thank you in advance for your help.

Re: Bar Chart visualization

jyothish8807 — Wed, 03 Jun 2015 09:34:18 GMT

Hi Nagaian,

You can try something like this, because my numbers are not correct its abut up down. I have overlapped two charts.

Hope it helps.

Regards

Re: Bar Chart visualization

jyothish8807 — Wed, 03 Jun 2015 09:38:34 GMT

PFA

Regards

Re: Bar Chart visualization

nagaiank — Wed, 03 Jun 2015 09:53:33 GMT

Thank you KC. By overlapping two charts, though I may be able to get the region total order amount on the top of the bars, I am unable to display order counts near each bar segment and on the top of the bar.

Re: Bar Chart visualization

stigchel — Wed, 03 Jun 2015 10:20:21 GMT

This looks like what you want, it is using three charts overlapping. One for the total on top, an other for numbers on the right and the original chart. It may need some tweaking...

Re: Bar Chart visualization

Kushal_Chawda — Wed, 03 Jun 2015 11:28:35 GMT

Another way to do is to create 4 text in chart for each region and adjust the same on bar

Re: Bar Chart visualization

stigchel — Wed, 03 Jun 2015 11:31:42 GMT

To explain a bit further, the text on the right side is a barchart with transparent colors and uses a dual to have the bars the same size as the original chart but the numbers displaying the Order Count:

Dual([Order Count],Sum([Order Amount]))

The chart for the numbers on top uses two expressions, one to have the bar the same size as the original (Sum([Order Amount])), the other has no bar, but has values on datapoints checked to display the desired text on top of the bar

Sum(TOTAL <Region> [Order Amount])&'/'&Sum(TOTAL <Region> [Order Count])

The values inside segments in the presentation tab is unchecked for this one.

Re: Bar Chart visualization

giakoum — Wed, 03 Jun 2015 11:50:48 GMT

Solving the region totals is easy :

Add this expression : Aggr(Sum([Order Amount]), Region) & '/' & Aggr(sum([Order Count]), Region)

and set the check boxes as follows :

result :

Showing the order count by using a pop up, is that ok for your requirements?

Re: Bar Chart visualization

pratap6699 — Wed, 03 Jun 2015 12:32:56 GMT

hai hein very nice presentation....but i didn't get the proper result will u explain each step by navigation...i got the inside bar plot numbers...but i didn't get rightside of bar numbers(10,5,8,5) and top of bars (310/28) how it eill be...give me navigation,,dim-->region expressions-->?

Re: Bar Chart visualization

stigchel — Wed, 03 Jun 2015 12:46:17 GMT

The dimensions are in all three charts Region and Order Duration, the rest is like I explained in my post above, so

For the 10,5,8,5 one expression =Dual([Order Count],Sum([Order Amount]))

For the 310/28 two expressions, the first display as bar, the second no bar but values on datapoints:

1:size of bar:=Sum([Order Amount])

2:text display:=Sum(TOTAL <Region> [Order Amount])&'/'&Sum(TOTAL <Region> [Order Count])

Re: Bar Chart visualization

pratap6699 — Wed, 03 Jun 2015 12:50:58 GMT

thank u so much HEIN it's working

Re: Bar Chart visualization

nagaiank — Wed, 03 Jun 2015 13:08:02 GMT

Thank you all for your time and for helping me resolve the issue to meet the business requirements for the application.

Re: Bar Chart visualization

jyothish8807 — Thu, 04 Jun 2015 06:15:07 GMT

Hi NagaianK,

There is always an issue while copying the chart from app to ppt when you create a chart by overlapping multiple chart.

Do you have any solution for this? Even i am stuck with this issues. I tried few macros but couldn't find a solution.

Regards

Re: Bar Chart visualization

stigchel — Thu, 04 Jun 2015 07:27:12 GMT

For the export to excel you can add the two extra expressions to the top layer chart, with the option text as popup (no bars).

Re: Bar Chart visualization

Kushal_Chawda — Thu, 04 Jun 2015 11:27:44 GMT

Hi,

Writing some logic in script and some tricks on front end it is possible to achieve using the single chart. I have done the same. Even though you got the correct answer you can check it out

Re: Bar Chart visualization

stigchel — Thu, 04 Jun 2015 12:25:09 GMT

Yes, I actually had something similar, but did not like reloading some data twice, although it's ok with this example. I also think it can probably be done in one chart without loading extra data, using some synthethic dimensions, but that would get messy in the expressions.

Re: Bar Chart visualization

Kushal_Chawda — Thu, 04 Jun 2015 12:30:55 GMT

Yes, If actual data is huge then it will affect the loading performance. Otherwise its good to go.