Trying to sum up Revenue based on conditions

[Anonymous]

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

[hector_munoz · Accepted solution]

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

[Anonymous]

Thanks everything is now working correctly.

I really do appreciate you taking the time out of your day to assist me.