topic Re: Previous Month Sum in QlikView

Previous Month Sum

Anonymous — Mon, 22 Jun 2015 17:57:18 GMT

I need to sum the dollar amount of the Previous Month based on the selected date(GLFileDate).

I set up a variable for PriorMonth
(vPriorMonth)- =month(addmonths(max(GLFileDate),-1))

Set an expression below, but it returns $0.00

=sum({$<GLFileDate=($(vPriorMonth))>}[Closing Balance USD])

I am able to get the Expression title to render the previous month, so I think the variables that I set up are functioning properly

='Previous Month:
$(vPriorMonth) $(vMaxDay), $(vPriorMonthYear)'

returns Previous Month: May 15, 2015 when 6/15/2015 is selected

Any suggestions?

Dan

sunny_talwar — Mon, 22 Jun 2015 18:14:46 GMT

Hi, GLFileDate seems to be a date field and you are comparing date field to just a month. You probably would need to do something like this:

=Sum({$<GLFileDate={"=Month(GLFileDate) = $(vPriorMonth)'}>}[Closing Balance USD])

sunny_talwar — Mon, 22 Jun 2015 18:16:53 GMT

Or you can set your variable definition as this:

vPriorMonth =Date(AddMonths(Max(GLFileDate),-1), 'sameDateFormatasGLFileDate')

and then use

=Sum({$<GLFileDate={"$(vPriorMonth)"}>}[Closing Balance USD])

HTH

Best,

Sunny

Anonymous — Mon, 22 Jun 2015 18:26:11 GMT

Thank you Sunny- this worked!

Or you can set your variable definition as this:

vPriorMonth =Date(AddMonths(Max(GLFileDate),-1), 'sameDateFormatasGLFileDate')

and then use

=Sum({$<GLFileDate={"$(vPriorMonth)"}>}[Closing Balance USD])