topic Partial Sum Charting in QlikView

Partial Sum Charting

tracycrown — Tue, 01 Jun 2010 02:49:26 GMT

Dear all

Can someone help to show me an example in QV on how to chart several partial sum (ie. sub-total and grand-total) ?.

Thank you very much, Tracy

Partial Sum Charting

prabhu0505 — Tue, 01 Jun 2010 06:35:55 GMT

Hi Tracy,

To achieve your expectaton, you have to use Pivot table sheet object.

Go through the Online sales example to learn more on the object.

Partial Sum Charting

tracycrown — Tue, 01 Jun 2010 08:15:59 GMT

Dear Saravana Prabhu

Yes, I know how to do it in pivot table but not Bar or Line chart, please help.

Thank you very much, Tracy

Partial Sum Charting

prabhu0505 — Tue, 01 Jun 2010 08:23:10 GMT

Actually pivot table is a kind of object that is used for this purpose. But if we want to use other object then we have to cook it for our purpose.

So can you please be more specific on what is requirment?

Please explain it with an example, if possible.

Partial Sum Charting

tracycrown — Thu, 03 Jun 2010 05:24:59 GMT

Dear Saravana Prabhu

Please refer to the attached excel worksheet and questions in QV.

Thank you very much, Tracy

Partial Sum Charting

tracycrown — Thu, 03 Jun 2010 05:27:38 GMT

Dear Saravana Prabhu

Attached excel worksheet as it only allows one attachement at a time.

Thank you very much, Trac

Partial Sum Charting

prabhu0505 — Thu, 03 Jun 2010 10:20:35 GMT

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/6433.Copy-of-Example_5F00_2D00_5F00_2_5F00_I-suggest.xls:550:0]

Partial Sum Charting

prabhu0505 — Thu, 03 Jun 2010 10:24:17 GMT

Go through the excel, where I changed your table structure.

Comment on that.

I hope that, if you follow this format, you can achieve the reports you are looking for.

Partial Sum Charting

tracycrown — Thu, 03 Jun 2010 13:18:55 GMT

Dear Saravana Prabhu

Thanks for your quick response.

Kindly advise is there a way to do it in QV rather than rearranging excel data which is not my intention,

Thank you very much, Tracy

Partial Sum Charting

Thu, 03 Jun 2010 14:20:16 GMT

If you want to have Income - COS - G&A you have to change the sign on COS and G&A values in your SQL select or load statement.

In your Chart A you have dimensions type and month, normally the total is not shown in such graphs as it will render the individual values rather small in comparison to the total.

In chart B simply remove the type-dimension to get totals per month.

For the grand total in the Pivot I only was able to create a single grand total adding a calculated dimension with the formula sum(TOTAL Data).

Regards

Jürg

Partial Sum Charting

tracycrown — Fri, 04 Jun 2010 01:51:04 GMT

Dear Juerg Maier JmiD

Many thanks for your help.

Just to share with you that users only want to see all Revenue (Income) and Expense items (COS and G&A) in positive signs so I can't change the sign at all during load statement in order to get the correct Grand Total. If you have better method, please show me in QV.

I don't quite understand how to create a single grand total using sum(TOTAL Data) in Table 1, can you show me in QV ?.

I have tried Chart B based on your recommendation and it works well. May I know is it possible to combine both charts together for users to select either Chart A or Chart B and how to do it in cyclic group chart ?.

Thank you very much, Tracy

Partial Sum Charting

Fri, 04 Jun 2010 10:19:51 GMT

Hi Tracy

Pivot:
You get totals by activating "Show Partial Sums" for Dimension Month in the Chart Properties / Presentation screen. Make sure to select first the Month in the dimension list before setting the flag.

I do not see a way to modify the totals calculation. I have cloned your pivot, removed the dimensions and modified the sum formula to make the correct calculation, does not look very nice however

sum(if(Type='COS' or Type='G&A',-Data,Data))

Charts:

I have added two buttons and made the charts shown conditionally (Properties / Layout / Show. The buttons set the variable vSum to either 1 or 2.

You could manipulate the dimensions with a macro but the conditional show is the easier approach. You can also create a cyclic group and use the selected group in the show condition, but it's not as straight forward as the buttons. Another option is to create a table with the 2 entries, a list box and check on the current selection. Make the list box as "Always one selected value". If this is grayed out select first a single element in the list to make it available.

Regards

Jürg

Partial Sum Charting

tracycrown — Sat, 05 Jun 2010 15:32:43 GMT

Dear Juerg Maier JmiD

Thanks for all your help. Kindly advise is there a way change COS & G&A to positive sign while keeping the same Grand-Total.

How about writing a statement in script such as :

[Income-COS] as [Gross Profit]

[Gross Income-G&A] as [Net Income]

[Income-COS-G&A] as [Net Income]

Thank you so much, Tracy

Partial Sum Charting

Sun, 06 Jun 2010 18:05:19 GMT

Hi Tracy

Where are you from? Working with QV the first time?

You can try to duplicate each record and create an additional Type like 'GProfit' with Desc = 'Total'

For this type you can then use the if() to get the correct value for the sum.

e.g.

Income / School Fees = Data <1000>
GProfit / Total = Data <1000>

COS / Salery = Data <600>
GProfit / Total = -Data <-600>

The Type GProfit with Desc = Total will then show as 400

This might however also create side effects (count, graphs)

I have not found a way to suppress the - sign in the pivot.

I would give it a try to talk the users into accepting the minus values. Look at a standard financial report and you will see the signs in place for things to deduct. Hiding this will make the app harder to understand and maintain.

One option could also be to show Income and COS/G&A in different columns per month to point out the +/-

Good luck, Regards

Juerg

Partial Sum Charting

tracycrown — Wed, 09 Jun 2010 03:24:36 GMT

Dear Juerg

Yes, I just started using Qlikview not long ago to analyse results for School and I am trying very hard to meet dateline.

I had gone through the QV on-line training as the manual is not useful (no example). As of today, I found it difficult to continue the assignment.

I know you are trying very hard to explain but I don't understand what you mean ?. Are you saying that I can create many variables (Total_COS & Total_G&A) under Expressions or Script ???. Can you attach a QV's example ?

Thank you very much for your help, Tracy

Partial Sum Charting

Wed, 09 Jun 2010 06:33:20 GMT

Hi Tracy

Not variables, duplicate your data records by using

CONCATENATE

LOAD 'GP' as code

, if (code = 'Income',data,-data)

RESIDENT <your table>

You will get all records duplicated and can show the GP code as the correct total result instead of totalling.

Regards

Jürg

Partial Sum Charting

tracycrown — Thu, 10 Jun 2010 05:10:24 GMT

Dear Marier_Con

You have mentioned an interesting method, please advise what is the purpose of CONCATENATE ?. It will nice if you can you send me a QV example on GP (ie Income - COS).

Thank you very very much, Tracy

Partial Sum Charting

tracycrown — Thu, 10 Jun 2010 05:16:33 GMT

Dear Jurg

You have recommended an interesting method, can you send me a QV example just to compute GP (ie. Income - COS) ?.

Thank you very very much, Tracy

Partial Sum Charting

tracycrown — Fri, 18 Jun 2010 11:08:26 GMT

Dear Jurg

Any chance to show me an example in QV ?.

Many Thanks, Tracy