Qlik Community

Ask a Question

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 
Search instead for 
Did you mean: 
sculptorlv
Creator III
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

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

Check !

Not applicable

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
Creator III
Creator III
Author

Something goes wrong

Test.jpg

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
Creator III
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 nrContract nrMoneyTotal Count by Client
Client1108911451
Client1532421351
Client14.23E+081351
Client19894321151
Client282341026
Client2823926
Client223508726
Client39832505120

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.