Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
AAAif transaction_type "Open" /0.8,  If transacation_type "Private" /0.93,If transacation_type "First" /0.95
BBB20%
CCC13%
DDD10%

End result:

   

companytransaction_typeNet RevenueGross Revenue
AAAOpen$ 100.00$ 80.00
AAAPrivate$ 100.00$ 93.00
AAAFirst$ 100.00$ 95.00
BBBOpen$ 100.00$ 80.00
CCCOpen$ 100.00$ 87.00
DDDFirst$ 100.00$ 90.00
DDDOpen$ 100.00$ 90.00
DDDPrivate$ 100.00$ 90.00

Any help is greatly appreciated

11 Replies
hector_munoz
Specialist
Specialist

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
Not applicable
Author

Thanks everything is now working correctly.

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