topic Re: Pie Chart Help in QlikView

Pie Chart Help

Wed, 01 Feb 2012 12:29:55 GMT

Hi All

I am new to Qlikview and battling with the creation of a piechart.

Problem description:

I have a number of transactions, lets say 100 of them. Each transaction has a reason (Price, Delivery, Relationship). The reasons are not mutually exclusive, therefore for the 100 transactions there could be say 125 reasons selected.

I need to create a piechart that shows the percentage of each reason.

So lets presume the 125 reasons are split into (80 price, 20 delivery, 25 relationship). In excel i would simply have graphed the 2 columns below

Header 1	Header 2
Price	80
Delivery	20
Relationship	25

In Qlikview I need to select a dimension and an expression.

I have a number of transaction description fields, like name, department, transaction id etc, and I 3 fields which are called price, delivery, relationship. For each transaction these fields are either set to a 1 or 0 depending on whether or not they are selected.

How would I turn this into a piechart to acheive the above aim?

Any help much appreciated

Thanks

Re: Pie Chart Help

gargi_bardhan — Wed, 01 Feb 2012 13:09:22 GMT

Hi!

In dimension take Header1 & in expression try this

=dual( sum([Header 2]) & chr(10) &

num( sum([Header 2])/sum(total [Header 2]), '##%')

, sum([Header 2]))

In the expression tab 'relative' should be unchecked.

I guess I understood your problem correctly!

Regards,

Gargi

Pie Chart Help

Wed, 01 Feb 2012 13:31:51 GMT

Hi JP,

normally I would use the fields as you describe as name, department, transaction id as dimensions, but Gargi can be right and use the Header 1 as a dimension, but if I understand it correclty you should use a formula that sums the flags(1 and 0) from the fields price, delivery, relationship.

KR Gert

Pie Chart Help

hic — Wed, 01 Feb 2012 14:37:22 GMT

Or simpler: Do not add any dimension at all, and then add three expressions: Sum(Price), Sum(Delivery), Sum(Relationship). Then display it as relative (Chart properties – Expressions – Relative) and finally set it to percentage (Chart properties – Number – Integer / Show in percent).

Pie Chart Help

Wed, 01 Feb 2012 15:12:51 GMT

Henric thanks for this. This almost works.

If I set all 3 expressions to relative, i get them all showing 100%, and they all have an equal slice of the pie.

If I don't set them to relative then I get the correct size for the pie slices (exactly what I was after), but I get %'s that are not relative to each other, like 2500%. If I turn off the %, then I just get the numbers i.e. 25

Any further suggestions?

Thanks

Re: Pie Chart Help

hic — Wed, 01 Feb 2012 15:42:31 GMT

You're right - it shows 100% for all three slices.

Well, then there are two ways to go: Either you make your pie chart expression more complex by normalizing it yourself, i.e. by dividing with the proper number. It could be e.g.
Sum(Price)/Count(TransactionID).

Or you do something in the script to sput all three reasons into one field. How to do this depends very much on how your data is delivered. In the attachment you can find a mock-up for one case.

Pie Chart Help

Wed, 01 Feb 2012 16:01:47 GMT

Excellent the normalizing worked a treat, but i think your suggestion of moving the 3 fields into 1 has additional value, because at the moment if you click one of the slices on the pie chart it does not add to the selection filter which could be extremely useful.

Thanks a mil for your help Henric.