scripting

[#1]

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

data is like this how to do ,

Name	Year	Start	End	Commission
Peter	2014	0	60000	1.0%
Peter	2014	60001	100000	1.5%
Peter	2014	100001	150000	2.0%
Peter	2015	0	50000	1.0%
Peter	2015	50001	100000	1.5%
Peter	2015	100001	150000	2.0%
Tom	2014	0	50000	1.2%
Tom	2014	50001	100000	1.6%
Tom	2014	100001	150000	2.5%
Tom	2015	0	50000	1.2%
Tom	2015	50001	100000	1.6%
Tom	2015	100001	150000	2.5%
Mark	2014	0	50000	0.8%
Mark	2014	50001	80000	1.4%
Mark	2014	80001	140000	2.0%
Mark	2015	0	50000	0.8%
Mark	2015	50001	80000	1.4%
Mark	2015	80001	140000	2.0%

[Anonymous]

hi,

try this:

CumulativeSum:

NoConcatenate

LOAD

    Name,

    Date,

    Year,

    Sales,

    Name & '|' & Year as %NameYear,

    If(Name <> Previous(Name) or Year <> Previous(Year), Sales, RangeSum(Sales, Peek('CumulativeSum'))) AS CumulativeSum  

Resident Temp1;

given your Table above then write:

intervalls:

Intervallmatch(CumulativeSum, %NameYear)

load*

;

load

Start,

End,

Name & '|'  &  as %NameYear,

Commission

Resident myintervalltable; //this is the table you posted above

left join (CumulativeSum)

%NameYear,

Commission

Resident intervalls;

drop table intervalls;

This will however only work when you only have one Value for CumulativeSum per every Name/Year.

Best

Stefan