topic Re: Calculation based on two dimensions in QlikView

Calculation based on two dimensions

sculptorlv — Tue, 02 Feb 2016 08:51:53 GMT

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?

Re: Calculation based on two dimensions

Anonymous — Tue, 02 Feb 2016 08:56:43 GMT

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

Re: Calculation based on two dimensions

avinashelite — Tue, 02 Feb 2016 09:05:35 GMT

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

)

Re: Calculation based on two dimensions

MK_QSL — Tue, 02 Feb 2016 09:13:31 GMT

Check !

Re: Calculation based on two dimensions

Tue, 02 Feb 2016 09:31:49 GMT

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

Re: Calculation based on two dimensions

sculptorlv — Tue, 02 Feb 2016 12:05:58 GMT

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

Re: Calculation based on two dimensions

sculptorlv — Tue, 02 Feb 2016 12:06:54 GMT

Thanks for replay.

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

Re: Calculation based on two dimensions

Wed, 03 Feb 2016 12:58:21 GMT

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.