topic Re: Problem calculating margin in QlikView

Problem calculating margin

Mon, 09 Jan 2017 22:07:17 GMT

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

Re: Problem calculating margin

sunny_talwar — Mon, 09 Jan 2017 22:24:26 GMT

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?

Re: Problem calculating margin

Mon, 09 Jan 2017 22:39:09 GMT

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?

Re: Problem calculating margin

sunny_talwar — Mon, 09 Jan 2017 22:46:18 GMT

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

Re: Problem calculating margin

rwunderlich — Mon, 09 Jan 2017 22:47:03 GMT

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

-Rob

Re: Problem calculating margin

Mon, 09 Jan 2017 22:48:20 GMT

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

Thanks

Re: Problem calculating margin

Mon, 09 Jan 2017 22:49:37 GMT

That's not so good then... OK

Re: Problem calculating margin

rwunderlich — Mon, 09 Jan 2017 22:52:37 GMT

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

Re: Problem calculating margin

Mon, 09 Jan 2017 23:03:08 GMT

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.

Re: Problem calculating margin

rwunderlich — Tue, 10 Jan 2017 16:46:40 GMT

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