topic monthly-increment value aggregated by dimensions in QlikView

monthly-increment value aggregated by dimensions

Thu, 29 Sep 2016 11:06:47 GMT

Dear all,

I have a table like this:

SRC:
LOAD * INLINE [
Dim1, Dim2, Dim3, Thema1, Thema2, MSNr
400, G11, BF, 10, 20, 01.16
400, G11, BF, 15, 152, 02.16
400, G11, BF, 99, 99, 03.16
500, G11, BF, 1, 2, 01.16
500, F10, BF, 30, 21, 02.16
500, F10, BF, 1, 1, 05.16
];

and need to get a new column SUM_Thema 1 which sums up

column Thema1 IF Derivat,ISt,MS are equal - SORTED BY Date

Thanks for your help

Oliver

Derivat	ISt	MS	MSNr	Thema1	Thema2	SUM Thema1
F10	500	BF	01.16	30	21	30
F10	500	BF	02.16	1	1	31
G11	400	BF	01.16	10	20	10
G11	400	BF	02.16	15	152	25
G11	400	BF	03.16	99	99	124
G11	500	BF	05.16	1	2	1

Re: monthly-increment value aggregated by dimensions

sunny_talwar — Thu, 29 Sep 2016 16:59:10 GMT

Try this out:

SRC:

LOAD ISt,

Derivat,

MS,

Thema1,

Thema2,

Date#(MSNr&'.2016', 'MM.DD.YYYY') as MSNr

INLINE [

ISt, Derivat, MS, Thema1, Thema2, MSNr

400, G11, BF, 10, 20, 01/16/2016

400, G11, BF, 15, 152, 02/16/2016

400, G11, BF, 99, 99, 03/16/2016

500, G11, BF, 1, 2, 01/16/2016

500, F10, BF, 30, 21, 02/16/2016

500, F10, BF, 1, 1, 05/16/2016

];

FinalSRC:

LOAD *,

If(ISt = Previous(ISt) and Derivat = Previous(Derivat) and MS = Previous(MS), RangeSum(Peek('SUM Thema 1'), Thema1), Thema1) as [SUM Thema 1]

Resident SRC

Order By ISt, Derivat, MS, MSNr ASC;

DROP Table SRC;