topic Re: Issue with Expressing Total Margin in a Straight Table in QlikView

Issue with Expressing Total Margin in a Straight Table

Fri, 20 Nov 2020 18:02:30 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 Price	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%

The problem I am encountering is that I am 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 if it can be done any other possible way, can anyone please guide me through that as well.

Thanks,

Zarar

Re: Issue with Expressing Total Margin in a Straight Table

Gysbert_Wassenaar — Wed, 20 Aug 2014 13:41:42 GMT

Can you post the document you're working on? See Preparing examples for Upload - Reduction and Data Scrambling for how to do that safely.

Re: Issue with Expressing Total Margin in a Straight Table

JonnyPoole — Wed, 20 Aug 2014 13:46:11 GMT

If you use an aggregation function, it will know how to aggregate the details in the total line

replace: '([Total Net Price]-[Total Cost])/[Total Net Price]

with sum( [Total Net Price]-[Total Cost]) / sum( [Total Net Price])

Re: Issue with Expressing Total Margin in a Straight Table

Anonymous — Wed, 20 Aug 2014 13:49:50 GMT

see attached. it shows 39%.

Re: Issue with Expressing Total Margin in a Straight Table

Thu, 21 Aug 2014 07:35:28 GMT

Hi Gysbert and others,
The file is attached in this comment. Can you please have a look and try to solve this issue.

Thanks,
Zarar

Re: Re: Issue with Expressing Total Margin in a Straight Table

Gysbert_Wassenaar — Thu, 21 Aug 2014 09:05:52 GMT

See attached qvw.

Re: Issue with Expressing Total Margin in a Straight Table

Thu, 21 Aug 2014 09:26:20 GMT

Hi Gysbert !
Thanks a lot for fixing this up. It is fixed now and I have been struggling with it for so long. Massively appreciate your help.

Regards
Zarar