Qlik Community

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

1 Solution

Accepted Solutions
Highlighted
hector_munoz
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

View solution in original post

11 Replies
Highlighted
hector_munoz
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
harrytye
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
harrytye
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
hector_munoz
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
harrytye
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
hector_munoz
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
harrytye
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);