# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
New Contributor II

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

1 Solution

Accepted Solutions
Highlighted
Valued Contributor

## Re: Trying to sum up Revenue based on conditions

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

11 Replies
Highlighted
Valued Contributor

## Re: Trying to sum up Revenue based on conditions

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

Highlighted

## Re: Trying to sum up Revenue based on conditions

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
New Contributor II

## Re: Trying to sum up Revenue based on conditions

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.

Highlighted

## Re: Trying to sum up Revenue based on conditions

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
New Contributor II

## Re: Trying to sum up Revenue based on conditions

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!

Highlighted
Valued Contributor

## Re: Trying to sum up Revenue based on conditions

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

Highlighted
New Contributor II

## Re: Trying to sum up Revenue based on conditions

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.

Highlighted
Valued Contributor

## Re: Trying to sum up Revenue based on conditions

Hi Harry,

I attach the app I have done for the test.

Regards,
H

Highlighted
New Contributor II

## Re: Trying to sum up Revenue based on conditions

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