topic Re: GROSS PROFIT in QlikView

GROSS PROFIT

Wed, 07 Sep 2011 10:51:33 GMT

I have more than 100,000 lines showing invoice data. Few lines are shown below.

Invoice NO	Purchase Amount	Sold Amount
INV001	100	125
INV002	125	130
INV003	140	148
INV004	150	165
INV005	200	250

I want table as below

Invoice NO	Purchase Amount	Sold Amount	% Profit
INV001	100	125	20.00%
INV002	125	130	3.85%
INV003	140	148	5.41%
INV004	150	165	9.09%
INV005	200	250	20.00%
TOTAL	715	818	12.59%

Kindly help me.

GROSS PROFIT

Wed, 07 Sep 2011 11:01:22 GMT

Use this:

([Sold amount]-[Purchase Amount])/[Sold Amount]

Go to Number Tab:

Select Fixed to: 2 Dec

Tick on checkbox "Show in %age"

GROSS PROFIT

Wed, 07 Sep 2011 11:08:09 GMT

This is already done and I am getting TOTAL for "Purchase Amount" and "SOLD AMOUNT" but not getting total for % PROFIT.

GROSS PROFIT

Wed, 07 Sep 2011 11:32:53 GMT

Hi,

I think Erika giving the correct answer, if not that is exactly, what you want?

A little more information would be good.

Regards

Gabriel

GROSS PROFIT

Wed, 07 Sep 2011 11:39:17 GMT

I want total at bottom for Purchase Amount, Sold Amount & % Profit.....

GROSS PROFIT

Wed, 07 Sep 2011 11:41:15 GMT

If you are using a pivot table, you need to go to presentation tab and click show partial summaries for the top dimension, or change the table to a straight table

GROSS PROFIT

Wed, 07 Sep 2011 11:41:47 GMT

Use:

if u r using Pivot table:

Sum(

([Sold amount]-[Purchase Amount])/[Sold Amount]

)

GROSS PROFIT

Wed, 07 Sep 2011 11:51:05 GMT

Thanks for your response. Sorry but I am not getting TOTAL by using PIVOT TABLE for % Profit

Re: GROSS PROFIT

its_anandrjs — Wed, 07 Sep 2011 11:52:31 GMT

Hi,

See the attached sample for clearity

And suntotal of Purchase Amount, Sold Amount & % Profit.....

By the help of straight table you get this in pivot it is not possible for the expression, there you do only on dimensions.

Regards

Anand

GROSS PROFIT

Wed, 07 Sep 2011 11:55:26 GMT

Can u upload ur Sample App.

Re: GROSS PROFIT

Wed, 07 Sep 2011 12:01:57 GMT

Dear Anand, Thanks for your reply by sample attachment.

Kindly note that the Total % Profit should be 12.59% and not 58.34% shown in your example.

Dear Erika, kindly use sample shown by Anand.

Again, thanks for your response.

Re: GROSS PROFIT

Wed, 07 Sep 2011 12:12:51 GMT

Try something like....

Re: GROSS PROFIT

its_anandrjs — Wed, 07 Sep 2011 12:12:57 GMT

Hi,

See the attached new updated file.

Regards

Anand

Re: GROSS PROFIT

Wed, 07 Sep 2011 12:21:35 GMT

Dear Anand,

Thanks for your quick response. But the total for % profit you shown is actually AVERAGE of % Profit. I need total of % profit which should be : (818-715)/818 = 12.59%.

Re: GROSS PROFIT

Wed, 07 Sep 2011 12:25:07 GMT

Dear Gabriel,

Good Try, but this will not solve my problem as I want total at bottom of % Profit column.

Also, it will unnecessary take more time for script to execute as I have 100,000 records....

Re: GROSS PROFIT

Wed, 07 Sep 2011 12:26:28 GMT

I am on personal so I can not see the sample data.

Try adding a dimension as ='Total'

Change the style of the pivot to indent mode.

make the expression for percent:

if(left(Invoice,3)<>'INV', (sum([Sold amount])-sum([Purchase Amount]))/sum([Sold Amount]),

Sum(([Sold amount]-[Purchase Amount])/[Sold Amount]))

This should tell it for the dimensions invoice to use the previous expression that worked, but if it is on the total of the new dimension total, to use the sum of each.

Give that a try and see if it works.

Re: GROSS PROFIT

its_anandrjs — Wed, 07 Sep 2011 12:32:44 GMT

Hi,

I got it see my next update file there is a litle trick but values are accurate i use this formula.

See the updated one.

Reagrds,

Anand

Re: GROSS PROFIT

Wed, 07 Sep 2011 12:46:10 GMT

Dear Anand,

Understood your trick but need right answer... LOL....

GROSS PROFIT

Wed, 07 Sep 2011 12:50:47 GMT

is absolutely necessary to show the end of the column of profit? you can display on atable without dimension, only with expression

regards

Re: GROSS PROFIT

its_anandrjs — Wed, 07 Sep 2011 12:59:58 GMT

Hi,

It is a trick by the way you can use like this.

Regards

Anand