# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for
Did you mean:
Creator III

## Calculation based on two dimensions

Hello, need help again.

I have the following data (two dimensions and one evaluated column):

Client nr.     Contract nr.      Money

Client 1          010891               14\$

Client 1          053242               13\$

Client 1          423098532          13\$

Client 1          0989432              11\$

Client 2          08234                 10\$

Client 2          0823                    9\$

Client 2          23508                   7\$

Client 3          09832505               120\$

Now I need make similar Sum table in qlikview with additional column "bonus".

This bonus should  be evaluated based on sum of money to each client and each contract...

If sum of Client is more than 30\$, then each contract get 10% bonus to contract sum.

If sum of Client is more than 100\$, then each contract get 20% bonus to contract sum.

The result:

Client nr.     Contract nr.      Money     Bonuss

Client 1          010891               14\$          1.4\$

Client 1          053242               13\$          1.3\$

Client 1          423098532          13\$          1.3\$

Client 1          0989432              11\$          1.1\$

Client 2          08234                 10\$          0\$

Client 2          0823                    9\$              0\$

Client 2          23508                   7\$               0\$

Client 3          09832505              120\$          24\$

How can I get such expressions in qlikview?

7 Replies
Anonymous
Not applicable

try with this expression

if (aggr(sum(Money),[Client nr]<30, 0,

if (aggr(sum(Money),[Client nr]<100, sum(Money)*0.1,sum(Money)*0.2

MVP

try like this :

IF(aggr(sum(Money),[Client nr]) >30,sum(Money)*0.1,

If(aggr(sum(Money),[Client nr]) >100,sum(Money)*0.2,0

)

)

MVP

Check !

Not applicable

Hi Ruslans,

------------- Script Start ------------------

input:

[Contract nr] as contract_nr,

Money as money

FROM

(ooxml, embedded labels, table is Sheet1);

groupby:

join

if(sum(replace(Money,'\$',''))>30 and sum(replace(Money,'\$',''))<100,0.1,if(sum(replace(Money,'\$',''))>100,0.2,0)) as totalmoneyperclient

FROM

(ooxml, embedded labels, table is Sheet1) group by [Client nr];

finaloutput:

contract_nr,

money,

(replace(money,'\$','')*totalmoneyperclient)&'\$' as bonus

Resident input;

----------------- Script end -----------

Thanks,

Sreeman

Creator III
Author

Something goes wrong

It gives correct answer only for the first row...

Used expression:

if (aggr(\$(PET_Sold_Fulfillment),[Sales_Agent_Name]) < PET_C1, 'low', 'hight' )

Sales_Agen_Name = Client ...

These formulas are predefined in SQL

// SET PET_Sold_Fulfillment = '\$(PET_Sold_Fact)/\$(PET_Sold_Budget)';

// SET PET_Sold_Budget = 'SUM(if((Budget_Category = 5.1 OR Budget_Category = 5.2), Budget_Money, 0))';

// SET PET_Sold_Fact = 'SUM(if((Sale_Category = 5.1 OR Sale_Category = 5.2), Sale_Money, 0))';

Creator III
Author

Thanks for replay.

I really want to get expression within the graphical qlikview, because my SQL script already is heavy.

Not applicable

Hi Ruslans,

If it is a SQL Script why dont you join like below and get the result like and u will add the calculation in an expression.

SQL Script:

select a.empid, a. ename, a.salary, b.total_sum

from employee a , (select empid, sum(salary) as total_sum from employee group by empid) b

where a.empid=b.empid

Output of the SQL Script:

 Client nr Contract nr Money Total Count by Client Client1 10891 14 51 Client1 53242 13 51 Client1 4.23E+08 13 51 Client1 989432 11 51 Client2 8234 10 26 Client2 823 9 26 Client2 23508 7 26 Client3 9832505 120 120

And take a chart like Straight table and take the dimension of Client nr, Contract nr and Money

and next in the Expression write the below logic in the expression and label it as bonus.

(Replace(money,'\$','') * if([Total Count by Client]>30 and [Total Count by Client]<100, 0.1, if([Total Count by Client]>100,0.2,0.0)))&'%'

Thanks,

Sreeman.