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,
Try Advanced Interval match (Extended Syntax) topic in Qlikview Help file.
Temp:
LOAD Name,
Date,
year(Date) as Year,
Sales
FROM
(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 Order by Name,Date;
DROP Table Temp;
LEFT JOIN IntervalMatch (CumulativeSum, Name, Year)
LOAD
Start,
End,
Name,
Year
resident Key;
Regards,
Jagan.