Problem calculating margin

[#1]

Hi all,

I want to calculate the margin for a product sold (all products really) but I don't really get how I go about it.

From what I've gathered I should do a calculation similar to this:

(SalesPrice * Amount) * (1-rebate) - (SalesCost * Amount)

but where do i do this? all of the listed stuff is in one table (table A) except for SalesCost which is in another table (Table B)

All and any thoughts are appreciated.

Thanks

[sunny_talwar]

Try to avoid opening more than one thread for the same requirement. Kindly share some raw data or a sample application to see how your data model looks like.

Calculate margin in an expression?

[#3]

Yeah I just thought of the other one after already posting this one, and figured that they were a bit different at least, sorry.

I looked through this: Preparing examples for Upload - Reduction and Data Scrambling

But if I scramble the data, will it remain scrambled? I need it the way it is know...

And what do you mean by raw data? Tables?

[sunny_talwar]

it will remain scrambled. and by raw data I meant few rows in an Excel file

[rwunderlich]

If you are doing the calculation in a chart expression, it does not matter that the fields are in different tables.

-Rob

[#6]

That sounds great! Would you mind sharing how I can go about using such an expression?

Thanks

[#7]

That's not so good then... OK

[rwunderlich]

You would use it in a chart, such as a bar chart or a straight table.

1. Add one or more dimension fields ot the chart, for example, "Region" or "Product".

2. Add your calculation as an expression, wrapped by an aggregation function such as "sum()". For example:

sum((SalesPrice * Amount) * (1-rebate) - (SalesCost * Amount))

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[#9]

EDIT: When I replaced the " - " between the first part of the expression and the second with " / " it worked out.

EDIT 2: The stuff mentioned in the above edit didn't do anything but confuse me further... If anyone has an idea of how I can calculate my profit margins in percentages I would gladly take your advice

That gave a very strange looking bar chart!

This seems to give me each individual profit margin per product, correct?

I would want it shown as total sales margin percentage if that makes any sense?

Sorry if these questions seem stupid, I'm still trying to learn.

[rwunderlich]

I'm not sure re the specifics of your formula without seeing your data. If you upload a sample qvw with scrambled data, yes, the data will remain scrambled. Don't scramble the numeric fields. Just scramble the identifying text fields.  You don't need to upload the entire dataset, just enough to let us see some sample data.

If you want to see just the overall margin, then either:

1. Don't use a dimension in a straight table chart.

2. Use your expression in a text object. In this case you will need to use the num() function to format the results. Like:

=num(sum(your formula), '#.00%')

Nothing wrong with asking newbie questions here, but I'm wondering if you have done any introductory training for QV? A good place to start is the intro video series at New to QlikView Videos

When you are ready to dive deeper, I can recommend the book "QlikView Your Business" by Oleg Troyansky.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com