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]

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

[Anil_Babu_Samineni]

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)))))

[Anonymous]

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.

[Anil_Babu_Samineni]

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))))

[Anonymous]

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!

[hector_munoz]

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

[Anonymous]

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.

[hector_munoz]

Hi Harry,

I attach the app I have done for the test.

Regards,
H

[Anonymous]

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);