Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

GROSS PROFIT

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

Invoice NOPurchase AmountSold Amount
INV001100125
INV002125130
INV003140148
INV004150165
INV005200250

I want table as below

Invoice NOPurchase AmountSold Amount% Profit
INV00110012520.00%
INV0021251303.85%
INV0031401485.41%
INV0041501659.09%
INV00520025020.00%
TOTAL71581812.59%

Kindly help me.

23 Replies
Not applicable
Author

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.

Not applicable
Author

Hi

Try something like....

its_anandrjs

Hi,

See the attached new updated file.

Regards

Anand

Not applicable
Author

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%.

Not applicable
Author

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....

Not applicable
Author

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.

its_anandrjs

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

Not applicable
Author

Dear Anand,

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

Not applicable
Author

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

regards

its_anandrjs

Hi,

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

Regards

Anand