Calculation based on two dimensions

[sculptorlv]

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?

[Anonymous]

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

[avinashelite]

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

)

)

[MK_QSL]

Check !

[#5]

Hi Ruslans,

Please use the below script.

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

input:

LOAD [Client nr] as client_nr,

     [Contract nr] as contract_nr,

     Money as money

FROM

(ooxml, embedded labels, table is Sheet1);

groupby:

join

LOAD [Client nr] as client_nr,

      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:

load client_nr,

     contract_nr,

     money,

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

Resident input;

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

Thanks,

Sreeman

[sculptorlv]

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

[sculptorlv]

Thanks for replay.

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

[#8]

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.