Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
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%
1 Reply
s_kunte23
Creator III
Creator III

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