Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

bushpalaamarnat
Contributor

data subtraction calculation methos

Hi all,

Could any one please help.

Out put required is (week & material & Loc) week 6 - DFC Days should be subtracted from  (week & material & Loc) week 7  - DFC Days. the out put of this subtraction is in the new column. This way it should subtracted from week 8 - week 7, week 9- week 8 for 52 weeks.


Data Examples of input


    

Input
WeekMaterialLocDFC Days
W06-181234USD4
W06-182345IND230
W06-183456FR450
W06-181234UK36
W06-189568UK41
W07-181234USD9
W07-182345IND237
W07-189568UK46
W06-183456FR455
W07-181234UK41
W08-189568UK51
W08-181234USD14
W08-182345IND244
W08-183456FR460
W08-181234UK46

Out put required is as follows

     

Output
WeekMaterialLocDFC DaysDifference in Days
W06-181234USD45
W06-182345IND2307
W06-183456FR4505
W06-181234UK365
W06-189568UK415
W07-181234USD95
W07-182345IND2377
W07-189568UK465
W06-183456FR4555
W07-181234UK415
W08-189568UK51-51
W08-181234USD14-14
W08-182345IND244-244
W08-183456FR460-460
W08-181234UK46-46
1 Solution

Accepted Solutions

Re: data subtraction calculation methos

Try this

Table:

LOAD * INLINE [

    Week, Material, Loc, DFC Days

    W06-18, 1234, USD, 4

    W06-18, 2345, IND, 230

    W06-18, 3456, FR, 450

    W06-18, 1234, UK, 36

    W06-18, 9568, UK, 41

    W07-18, 1234, USD, 9

    W07-18, 2345, IND, 237

    W07-18, 9568, UK, 46

    W07-18, 3456, FR, 455

    W07-18, 1234, UK, 41

    W08-18, 9568, UK, 51

    W08-18, 1234, USD, 14

    W08-18, 2345, IND, 244

    W08-18, 3456, FR, 460

    W08-18, 1234, UK, 46

];


FinalTable:

LOAD *,

If(Material = Previous(Material) and Loc = Previous(Loc), Previous([DFC Days]) - [DFC Days], -[DFC Days]) as [Difference in Days]

Resident Table

Order By Material, Loc, Week desc;


DROP Table Table;

Capture.PNG

2 Replies

Re: data subtraction calculation methos

Try this

Table:

LOAD * INLINE [

    Week, Material, Loc, DFC Days

    W06-18, 1234, USD, 4

    W06-18, 2345, IND, 230

    W06-18, 3456, FR, 450

    W06-18, 1234, UK, 36

    W06-18, 9568, UK, 41

    W07-18, 1234, USD, 9

    W07-18, 2345, IND, 237

    W07-18, 9568, UK, 46

    W07-18, 3456, FR, 455

    W07-18, 1234, UK, 41

    W08-18, 9568, UK, 51

    W08-18, 1234, USD, 14

    W08-18, 2345, IND, 244

    W08-18, 3456, FR, 460

    W08-18, 1234, UK, 46

];


FinalTable:

LOAD *,

If(Material = Previous(Material) and Loc = Previous(Loc), Previous([DFC Days]) - [DFC Days], -[DFC Days]) as [Difference in Days]

Resident Table

Order By Material, Loc, Week desc;


DROP Table Table;

Capture.PNG

bushpalaamarnat
Contributor

Re: data subtraction calculation methos

Thank it worked for me.

Community Browser