Do not accumulate in accumulative dimension

[roeibenishti]

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

[petter]

[sunny_talwar · Accepted solution]

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)

[roeibenishti]

Thanks a lot. Easy and simple idea