topic Re: Express Total Margin within Straight Table in QlikView

Express Total Margin within Straight Table

Thu, 06 Feb 2014 15:17:02 GMT

Hi everyone,

I have a straight table which calulates gross margin per line.

When multiple lines are selected, it dispays the average % margin - 41%

How do you express the totals line to show the margin for the total value - in this case (Nett-Cost) / Nett, which should be 39%

Item	£ List	Nett Price	£ Cost	£ Margin
A	5,000	2,000	1,000	50%
B	3,000	2,000	1,000	50%
C	4,000	3,000	1,500	50%
D	2,000	1,900	1,500	21%
E	1,000	500	300	40%
F	500	400	200	50%
G	5,000	1,000	500	50%
H	5,000	4,000	2,900	28%
I	4,000	1,500	1,000	33%
Total		16,300	9,900	41%

Thanks,

Paul

Re: Express Total Margin within Straight Table

maxgro — Thu, 06 Feb 2014 15:26:19 GMT

(sum([Nett Price]) - sum(Cost)) / sum([Nett Price])

Re: Express Total Margin within Straight Table

Fri, 07 Feb 2014 09:58:08 GMT

Thanks Massimo,

This works well, the only issue I have is that my Nett price is derived from a calculation of ‘List Price less Discounted Value’.

Do I need to include the calculated fields in the formula below, so something like...

(sum() - sum(Cost)) / sum()

Regards,

Paul

Re: Express Total Margin within Straight Table

maxgro — Fri, 07 Feb 2014 10:46:20 GMT

I think

(sum([List Price] - [Discounted Value]) - sum(Cost)) / sum([List Price] - [Discounted Value])

another way

if you have a calculated field in your chart you can use it in the expressions in same chart using the label of that field

Re: Express Total Margin within Straight Table

Mon, 17 Feb 2014 11:21:23 GMT

Thanks for your response.

I have revisted my table but still have a few issues.

I have two input fields created, one for quantity and one for discount, The values for List Price and Cost are created by multiplying the input fields.

Should it be possible to see a calculated total margin, if input fields are contained within the expression?

Thanks,

Paul

Re: Express Total Margin within Straight Table

timsaddler — Mon, 17 Feb 2014 14:41:06 GMT

Hello

Putting the calculation for margin value into the script may help the data to roll up correctly?

Re: Express Total Margin within Straight Table

Wed, 20 Aug 2014 07:59:17 GMT

Hello Everyone,
I am working with Paul on this tool as well. The problem we are encountering is that we are not getting the total margin % at the bottom of all the individual margins. If I select 'Expression Total', it doesn't return any value but a '-'. If I select Sum of Rows, it literally adds up all the numbers which is not correct either. Selecting Average of Rows gives us the the Avg which is near to but still not correct Margin.

The formula for margin is : ''([Total Net Price]-[Total Cost])/[Total Net Price]'' where Total Net Price and Total Cost are also formulae. I heard that the Aggregation 'AGGR' function might be of help. So can anyone please guide me through AGGR function.

And Tim, can you please elaborate on what should be done in the script breifly.

Thanks,

Zarar

Re: Express Total Margin within Straight Table

timsaddler — Fri, 29 Aug 2014 13:03:32 GMT

Put the calculation into the script instead of the Expression box. Then the roll up should work.

Re: Express Total Margin within Straight Table

timsaddler — Fri, 29 Aug 2014 13:25:42 GMT

To explain better... here's an example from one of my scripts...

"Extended Price" as SalesExcLevy,

     "Aggregate Levy" as AggLevy,

     "Extended Price" + "Aggregate Levy" as SalesIncLevy,

     "Std Cost Haulage" as 'StdCostOfHaulage',

     "Split_Std_Cost_Of_Haulage" as 'SplitStdCostOfHaulage',

     "Actual_Cost_of_Haulage" as 'ActCostOfHaulage',

   ("Extended Price" + "Aggregate Levy") - "Std Cost Haulage" as StdExWorksSalesIncLevy,