
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Trying to sum up Revenue based on conditions
Hey everyone,
I am trying to create a dashboard for Sales in QlikView. The issue is that each company has a different rate of revenue.
For example I want a column that shows Sum(net_revenue) and then a column that shows Gross revenue based off a table like below. I am not sure if I need to do a proceeding load would it be a set analysis formula.
Company BBB makes $100 but the rate will show $100 x .8 = $80
Company AAA if transaction type showed Private would then be $100 x .93 = $93
Calculation table:
company | rule |
AAA | if transaction_type "Open" /0.8, If transacation_type "Private" /0.93,If transacation_type "First" /0.95 |
BBB | 20% |
CCC | 13% |
DDD | 10% |
End result:
company | transaction_type | Net Revenue | Gross Revenue |
AAA | Open | $ 100.00 | $ 80.00 |
AAA | Private | $ 100.00 | $ 93.00 |
AAA | First | $ 100.00 | $ 95.00 |
BBB | Open | $ 100.00 | $ 80.00 |
CCC | Open | $ 100.00 | $ 87.00 |
DDD | First | $ 100.00 | $ 90.00 |
DDD | Open | $ 100.00 | $ 90.00 |
DDD | Private | $ 100.00 | $ 90.00 |
Any help is greatly appreciated
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Harry,
Change this:
MAP_DISCOUNT:
MAPPING LOAD * INLINE [
DS Vendor + DS Transaction types, DS Rate
AAA|[Open auction], 0.8
AAA|[Private auction], 0.93
AAA|[First look], 0.95
BBB, 0.7
CCC, 0.75
DDD, 0.6
];
, for:
MAP_DISCOUNT:
MAPPING LOAD * INLINE [
DS Vendor + DS Transaction types, DS Rate
AAA|Open auction, 0.8
AAA|Private auction, 0.93
AAA|First look, 0.95
BBB, 0.7
CCC, 0.75
DDD, 0.6
];
You only have to use [] characters when field naming and they are only necessary when they contain spaces (in your case).
Regards,
H


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Harry,
You could create a MAPPING TABLE with a KEY formed by the concatenation of the fields company and transaction_type and when you load fact table make a call to an ApplyMap() function and get the rate for each record.
A sample of MAPPING TABLES are here: https://www.quickintelligence.co.uk/applymap-is-it-so-wrong/
Regards,
H
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be this?
Company is a Dimension
Expression is
If(transaction_type = 'Open', (Sum([Net Revenue]) * (Num(Sum([Gross Revenue]))/100)),
If(transaction_type = 'Private', (Sum([Net Revenue]) * (Num(Sum([Gross Revenue]))/100)),
If(transaction_type = 'First', (Sum([Net Revenue]) * (Num(Sum([Gross Revenue]))/100)))))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thats close. Each company has a different rate.
Companies BBB, CCC, DDD all have one set rate for all transaction types.
only Company AAA uses a different rate for each transaction type.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Then may be this?
Create Variable
SET differentrate = '0.87';
If(company <> 'ABC' and transaction_type = 'Open' and transaction_type = 'Private', transaction_type = 'First', (Sum([Net Revenue]) * (Num(Sum([Gross Revenue]))/100)), (Sum([Net Revenue]) * $(differentrate)))
Or
If(Match(company,'BBB', 'CCC', 'DDD') and transaction_type = 'Open' and transaction_type = 'Private', transaction_type = 'First', (Sum([Net Revenue]) * (Num(Sum([Gross Revenue]))/100)), If(Match(company,'AAA',(Sum([Net Revenue]) * $(differentrate))))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How about an expression like this:
If([Transaction type] = 'Open' and Company = 'AAA', ((Sum([Net Revenue]) / (0.8))))
I would like to make this work:
If([Transaction type] = 'Open' and Company = 'AAA', ((Sum([Net Revenue]) / (0.8)))),
If([Transaction type] = 'First' and Company = 'AAA', ((Sum([Net Revenue]) / (0.95)))),
If([Transaction type] = 'Open, First, Private' and Company = 'BBB', ((Sum([Net Revenue]) / (0.8))))
I hope this makes sense to you!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Harry,
I would try to calculate Gross Revenue in script in a way like this (you can copy the following code and put in a blank QlikView application):
MAP_DISCOUNT:
MAPPING LOAD * INLINE [
DS Company + DS Transaction Type, DS Rate
AAA|Open, 0.8
AAA|Private, 0.93
AAA|First, 0.95
BBB, 0.7
CCC, 0.75
DDD, 0.6
];
FACTS:
LOAD *,
[Net Revenue] /
If(Len(ApplyMap('MAP_DISCOUNT', company & '|' & transaction_type, '')) > 0,
ApplyMap('MAP_DISCOUNT', company & '|' & transaction_type),
If(Len(ApplyMap('MAP_DISCOUNT', company, '')) > 0,
ApplyMap('MAP_DISCOUNT', company, ''),
1)) AS [Gross Revenue];
LOAD * INLINE [
company, transaction_type, Net Revenue,
AAA, Open, 100
AAA, Private, 100
AAA, First, 100
BBB, Open, 100
CCC, Open, 100
DDD, First, 100
DDD, Open, 100
DDD, Private, 100
];
Above, each company+transaction type or only company has its own rate. You have to associate, using a MAPPING TABLE and a ApplyMap() function, this rate to each row of the fact table and divide the net revenue by it...
Hope it serves!
Regards,
H

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for you help. This looks exactly what I want.
I will have to wait until Monday to full test it. I am not super familiar with mapping tables and my college who is is away. I tried a straight copy and paste and it doesn't work of course.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Harry,
I attach the app I have done for the test.
Regards,
H

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ok I figured out what I was doing wrong and loaded this Mapping before my table and the incorporated the "Facts" into my inital load.
In your example it works fine. However I don';t get the calculations now. Gross Revenue just mirrors Net Revenue.
This is the info directly from my QVW. Names changed to my data:
MAP_DISCOUNT:
MAPPING LOAD * INLINE [
DS Vendor + DS Transaction types, DS Rate
AAA|[Open auction], 0.8
AAA|[Private auction], 0.93
AAA|[First look], 0.95
BBB, 0.7
CCC, 0.75
DDD, 0.6
];
FACTS:
LOAD *,
[Net Revenue] /
If(Len(ApplyMap('MAP_DISCOUNT', Vendor & '|' & [Transaction types], '')) > 0,
ApplyMap('MAP_DISCOUNT', Vendor & '|' & [Transaction types]),
If(Len(ApplyMap('MAP_DISCOUNT', Vendor, '')) > 0,
ApplyMap('MAP_DISCOUNT', Vendor, ''),
1)) AS [Gross Revenue];
LOAD DateField,
[Site Tag],
eCPM,
Impressions,
[Transaction types],
[Net Revenue],
Vendor
FROM
------------------------------------\Revenue\Workbook.qvd
(qvd);

- « Previous Replies
-
- 1
- 2
- Next Replies »