Partial Sum Charting

[tracycrown]

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

[tracycrown]

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

[#3]

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

[tracycrown]

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

[#5]

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

[tracycrown]

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

[#7]

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

[tracycrown]

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

[tracycrown]

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

[tracycrown]

Dear Jurg

Any chance to show me an example in QV ?.

Many Thanks, Tracy