Measure based on previous month

[#1]

Hi,

I need to calculate the Count(ID) for each month in the year based on previous month's Count(ID) value

Month	End Value	Begin Value
Jan-16	100	-
Feb-16	200	100
Mar-16	300	200
Apr-16	400	300

In the above table:

End Value = Count(ID)

Begin Value = End Value of previous month

I need to create a measure that calculates the Begin Value.

How do I achieve this in Qlik Sense?

[hytown2000]

Do you want to generate this measure in load script?

[#3]

Yes. Generating it in load script would be good.

If we can achieve this using set analysis that will also be helpful.

[arulsettu]

for begin value

write this expression

above(Count(ID))

[jonathandienst · Accepted solution]

Assuming your month fields are valid numeric date values, then you could use:

LOAD Month,

  [End Value],

  Previous([End Value]) as [Begin Value],

  ...

FROM ...

Order By Month;

If your month values are strings, convert to date values

LOAD Date#(Month, 'MMM-yy') as Month,.

...

Order By Date#(Month, 'MMM-yy')

[#6]

Hi Soumya,

Refer - Jonathan details it will work.

If not Previous then use Peek function as well.

Thank you..Ravi Kancharla