topic Re: Do not accumulate in accumulative dimension in QlikView

Do not accumulate in accumulative dimension

roeibenishti — Tue, 05 Dec 2017 20:12:20 GMT

Hi All,

I have the following tables:

Time:

date2	date
NOV-2017	NOV-2017
DEC-2017	NOV-2017
DEC-2017	DEC-2017
JAN-2018	NOV-2017
JAN-2018	DEC-2017
JAN-2018	JAN-2018

Data:

date	Sales	Inventory.Value
NOV-2017	1,000	12,000
DEC-2017	1,500	10,500
JAN-2018	2,000	8,500

I'm using the date2 dimension in my pivot table so i'll get an accumulate sales result (I have to use this dimension since there are additional objects that use it and I cannot change them).

I'm trying to get the correct OB value for each one of the fields, but i'm only getting it accumulated.

What I'm trying to achive:

date2	Sales	Inventory.Value
DEC-2017	2,500	12,000
JAN-2018	4,500	10,500

How can I sort it so Inventory.value will present the previous month inventory value while I'm using the date2 dimension?

Thank for helping

Re: Do not accumulate in accumulative dimension

petter — Tue, 05 Dec 2017 23:01:57 GMT

Re: Do not accumulate in accumulative dimension

sunny_talwar — Wed, 06 Dec 2017 01:17:46 GMT

Another way would be to create a flag in the script

Time:

LOAD *,

(Year(date2)*12 + Month(date2)) - (Year(date)*12 + Month(date)) as Difference;

LOAD Date#(date2, 'MMM-YYYY') as date2,

Date#(date, 'MMM-YYYY') as date;

LOAD * INLINE [

date2, date

NOV-2017, NOV-2017

DEC-2017, NOV-2017

DEC-2017, DEC-2017

JAN-2018, NOV-2017

JAN-2018, DEC-2017

JAN-2018, JAN-2018

];

Data:

LOAD Date#(date, 'MMM-YYYY') as date,

Sales,

Inventory.Value;

LOAD * INLINE [

date, Sales, Inventory.Value

NOV-2017, "1,000", "12,000"

DEC-2017, "1,500", "10,500"

JAN-2018, "2,000", "8,500"

];

And then this

=Sum({<Difference = {1}>}Inventory.Value)

Re: Do not accumulate in accumulative dimension

roeibenishti — Wed, 06 Dec 2017 09:32:59 GMT

Thanks a lot. Easy and simple idea