Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi Qv,
Temp:
LOAD Name,
Date,
Year(Date) as Year,
Sales
FROM
Data.xlsx
(ooxml, embedded labels, table is [Dummy Sales]);
Temp1:
NoConcatenate
LOAD
*
Resident Temp
Order by Name, Date;
DROP Table Temp;
CumulativeSum:
NoConcatenate
LOAD
Name,
Date,
Year,
Sales,
If(Name <> Previous(Name) or Year <> Previous(Year), Sales, RangeSum(Sales, Peek('CumulativeSum'))) AS CumulativeSum
Resident Temp1;
DROP Table Temp1;
---------------------------------------------------------------------------------
i have done Cumulative sum for above and the problem is i need commission for Different names commision may vary how to give dynamic in set analysic plz help me
Name | Year | Slab | Commission |
Peter | 2014 | $0-$50000 | 1% |
peter | 2014 | $50001-$100000 | 1.5% |
peter | 2014 | $100001-$150000 | 2% |
peter | 2014 | >$150001 | 2.5% |
john | 2014 | $0-$50000 | 1.5% |
like this the commision varies plz help
-lohi
Hi,
Can you attach some sample file?
Try like this
Dimension : Name, Year
Expression:
If(Sum(Sales) > 150001, 0.025,
If(Sum(Sales) >= 100001, 0.02,
If(Sum(Sales) >= 50001, 0.015,
If(Sum(Sales) >= 0, 0.01))))
Hope this helps you.
Regards,
Jagan.
hi jagan ,
plz check the above table slap may varies i need dynamic expression ,
-lohi
Hi ,
PFA QVW . its contain example of dynamic bucketing. Hope that may help u .
Thanks!!
be practical in i have millions of users i have to create million groups , it is not possible
- lohi
Hi,
Try like this in script
Data:
LOAD
*,
If(Sales > 150001, '>$150001',
If(Sales >= 100001, '$100001-$150000',
If(Sales >= 50001, '$50001-$100000',
If(Sales >= 0, '$0-$50000')))) AS Slab;
LOAD
Name,
Year,
Sum(Sales) AS Sales
FROM DataSource
Group by Name, Year;
LEFT JOIN
LOAD
Name,
Year,
Slab,
Commission
FROM CommissionSource;
Hope this helps you.
Regards,
Jagan.
hi jagan ,
hope it helps but
Peter | 2014 | $0-$50000 | 1% |
john | 2014 | $0-$50000 | 1.5% |
if this the requirement the
peter will get 1%
john also get 1%
-lohi
Hi,
It should not happen, because we are join the Commission table based on Name, Year and Slab, so you will get different values for Name.
Check once again.
Regards,
jagan.
hi jagan ,
i have tried like this
Commision:
LOAD*,
if(CumulativeSum<=50000,'1%',if(CumulativeSum>=50001 and CumulativeSum<=100000,'1.5%',if(CumulativeSum>=100001 and CumulativeSum <=150000,'2%',if(CumulativeSum>=150001,'2.5%','NULL')))) As Comm
Resident CumulativeSum
Order by Name,Year
;
Comm:
LOAD * Resident Commision Order by Commision.Name,Commision.Year;
Commision1:
LOAD Commision.Name,
Commision.Date,
Commision.Year,
(Commision.Comm*Commision.CumulativeSum) as Cumm
Resident Commision
Order by Commision.Name,Commision.Year
;
but the problem is slap may varies depends on Users.
- lohi
hi jagan ,
i am forwarding the attachment
PFA ,
-lohi