Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Measure based on previous month

Hi,

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

MonthEnd ValueBegin Value
Jan-16100-
Feb-16200100
Mar-16300200
Apr-16400300

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?

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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')


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

5 Replies
hytown2000
Partner - Contributor III
Partner - Contributor III

Do you want to generate this measure in load script?

Not applicable
Author

Yes. Generating it in load script would be good.

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

arulsettu
Master III
Master III

for begin value

write this expression

above(Count(ID))

jonathandienst
Partner - Champion III
Partner - Champion III

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')


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Hi Soumya,

Refer - Jonathan details it will work.

If not Previous then use Peek function as well.

Thank you..Ravi Kancharla