topic Re: New to QV: Pivot Chart - Differentiating between Dimension and Expression in QlikView

New to QV: Pivot Chart - Differentiating between Dimension and Expression

Thu, 28 Jul 2011 16:49:37 GMT

I am trying to create a pie chart out of the following data. It is literally one line of data. I would like to display a pie chart where, the Number of Green Accounts is shown as a percentage of the Total Number of All Colours Entity Accounts. I have tried various ways, but I am not getting it right. Can anyone please help.

Line of Business	Type of Transaction	Number of Green Accounts	TotalNumber of AllColours Entity Accounts
Green Banking	Account Opening	66,000	6,000,000

Thank you

New to QV: Pivot Chart - Differentiating between Dimension and Expression

mazacini — Thu, 28 Jul 2011 17:12:32 GMT

Can you create a dimension to indicate y for Colour = Green and n for Colour <> Green?

Then simply create the pie chart for the Total of Accounts using the new dimension?

New to QV: Pivot Chart - Differentiating between Dimension and Expression

Thu, 28 Jul 2011 19:13:14 GMT

Hi Mazacini

I am really sorry - I don't really understand what you are asking me to do.

Are you saying I can create a dimension on the fly within the 'Properties' window?

Re: New to QV: Pivot Chart - Differentiating between Dimension and Expression

mazacini — Thu, 28 Jul 2011 19:54:45 GMT

I think you have 2 expressions for the dimensions Line of Business / Type of Transaction.

But for the pie chart, you should have 1 expression for a new dimension, with the dimension having 2 values.

So you table would look like this:

Line of Bus	Type of TX	NewDim	Amount
Green Banking	Account Opening	Green	66,000
Green Banking	Account Opening	Other	5,934.000

New to QV: Pivot Chart - Differentiating between Dimension and Expression

Thu, 28 Jul 2011 20:25:53 GMT

Hi Mazacini

thank you for your help so far. I've made more progress than I had previously, but I am still not there.

I have attached a screen print of the 'pie chart' as it stands now and the underlying data. Can you please nudge me in the right direction.

Re: New to QV: Pivot Chart - Differentiating between Dimension and Expression

mazacini — Thu, 28 Jul 2011 21:07:27 GMT

Can you show me screenshots of Dimensions and Expressions?

I think you should have one Dimension (Line of Business) and one Expression =sum(Number of Foreign Entity Accounts).

Re: New to QV: Pivot Chart - Differentiating between Dimension and Expression

Thu, 28 Jul 2011 21:32:17 GMT

Hi Mazacini

Thank you for your help so far. I have left work now, so cannot send you any screenshots tonight but from your response below, I know I'm totally off point. I have three expressions none of which is a sum. I have two dimensions (I think).

When I get back in tomorrow morning, I will try and implement what you have just sent to me.

Your help is much appreciated.

Natasha

Re: New to QV: Pivot Chart - Differentiating between Dimension and Expression

mazacini — Fri, 29 Jul 2011 08:52:44 GMT

Hi. I am around today.

Re: New to QV: Pivot Chart - Differentiating between Dimension and Expression

Fri, 29 Jul 2011 09:35:13 GMT

Hi Mazacini

Thank you.

Ok, I’ve amended the spreadsheet. I am trying to create 3 pie charts to reflect the 3 different types of transactions:

Here’s the s/sheet data:

Line of Business

Number of Foreign Entity Accounts

Type of Transaction

Regional Banking

66,000

Account Opening

Other

5,934,000

Account Opening

Regional Banking

10,000,000

Payments to Foreign Entities

Other

590,000,000

Payments to Foreign Entities

Regional Banking

No of Foreign Entity Vendors

Other

6,000

No of Foreign Entity Vendors

Do you still want to see the screen shots?

Natasha

Re: New to QV: Pivot Chart - Differentiating between Dimension and Expression

mazacini — Fri, 29 Jul 2011 09:48:05 GMT

Can you attache the spreadsheet?

Re: New to QV: Pivot Chart - Differentiating between Dimension and Expression

Fri, 29 Jul 2011 09:52:38 GMT

Hi Mazacini

I think I’ve got it working, but I will still attach the spreadsheet.

I’ll also include screenshots of the pie charts.

Thank you.

Natasha

Re: New to QV: Pivot Chart - Differentiating between Dimension and Expression

mazacini — Fri, 29 Jul 2011 11:22:04 GMT

Hi Natasha

You're nearly there!.

You are still using 2 dimensions, whereas i reckon you need to use only a single dimension "Line of Business".

Then you will need to use Set Analysis for each chart.

So, for Type of Transaction = Account Opening, enter this in expression:

Then create separate charts for Type of Transactions = Payments to Foreign Entities etc, replacing Account Opening in the above formula.

For what it's worth, some thoughts:

I would make the comment (respectfully) that maybe Qlikview is not the ideal solution in this case?

Whilst I would be a huge fan of QV, in this case I would suggest that Excel might be a better option? (See attached spreadsheet).

Anyway, let me know if you need any more help.

Re: New to QV: Pivot Chart - Differentiating between Dimension and Expression

Fri, 29 Jul 2011 12:28:51 GMT

Hi Mazacini

Thank you – you’ve been very helpful. I will implement the changes you suggested and let you know the outcome.

Re: QV vs Excel – I totally see where you’re coming from. I have been commissioned with evaluating QV, so I’ll be able to give an honest overview after this exercise J

I’ll update you soon.

Natasha

Re: New to QV: Pivot Chart - Differentiating between Dimension and Expression

Fri, 29 Jul 2011 14:14:12 GMT

Mazacini

everything works perfectly now . Thank you very much. You've been very helpful.

Much appreciated

Natasha