Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Thanks everything is now working correctly.
I really do appreciate you taking the time out of your day to assist me.