Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
)
)
Check !
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
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))';
Thanks for replay.
I really want to get expression within the graphical qlikview, because my SQL script already is heavy.
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.