Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

scripting

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
Peter20140600001.0%
Peter2014600011000001.5%
Peter20141000011500002.0%
Peter20150500001.0%
Peter2015500011000001.5%
Peter20151000011500002.0%
Tom20140500001.2%
Tom2014500011000001.6%
Tom20141000011500002.5%
Tom20150500001.2%
Tom2015500011000001.6%
Tom20151000011500002.5%
Mark20140500000.8%
Mark201450001800001.4%
Mark2014800011400002.0%
Mark20150500000.8%
Mark201550001800001.4%
Mark2015800011400002.0%
Tags (2)
1 Reply
s_kunte23
Contributor III

Re: scripting

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

Community Browser