topic Last month, last year function in QlikView

Last month, last year function

Tue, 10 Sep 2013 09:38:01 GMT

Hello guys,

i have some problems with the last year and last month function.

I want to create a table like the following, with the sales of several months.

For instance at September 2013 if i select january - august 2013 it should display the sales of the

current year in sales 2013 (january - august) und the sales of the last year in sales 2012 (january - august) .

And in the other two fields i want to calculate the sales of the last month (august 2013) and august 2012.

Use this expressions:

This two works fine, i mean if i select the january - mai 2010 i get the values for january - mai 2010 and january - mai 2009.

Sales 2013: Sum({$<Year={$(=Only(Year))}> } Sales)

Sales 2012: Sum({$<Year={$(=Only(Year-1))}> } Sales)

But it doesn't work by these experessions i, i get null. Except i select the current month.

August 2013: sum( {$ < Year = {$(=Only(Jahr))}, Month = {$(=month(AddMonths(today(), -1)))} >} Sales )

August 2012: sum( {$ < Year = {$(=Only(Jahr-1))}, Month = {$(=month(AddMonths(today(), -1)))} >} Sales )

I hope someone can help me.

Re: Last month, last year function

michael_maeuser — Tue, 10 Sep 2013 09:41:57 GMT

look at this example

Re: Last month, last year function

tresB — Tue, 10 Sep 2013 09:45:27 GMT

Try:

August 2013: sum( {$ < Year = {$(=Only(Jahr))}, Month = {'$(=month(AddMonths(today(), -1)))'} >} Sales )

August 2012: sum( {$ < Year = {$(=Only(Jahr-1))}, Month = {'$(=month(AddMonths(today(), -1)))'} >} Sales )

Note, the additional quotes.

Re: Last month, last year function

Tue, 10 Sep 2013 11:41:12 GMT

Thx for your example!

I have tasted it in my qvw and it works fine.

But there is still a problem.

When i select for instance january 2013 i get the same year in the field vPriorMonthyear and vPriorYear.

And because of this reason i get the sales for december 2012 two times.

Re: Last month, last year function

Tue, 10 Sep 2013 11:48:36 GMT

Thank you, but with this expressions i have still the same problem as in my first post

Re: Last month, last year function

michael_maeuser — Tue, 10 Sep 2013 11:50:45 GMT

what does your expression look like?

YTD should be like this:

SUM({<Year={'$(vMaxYear)'},Month={'$(vMaxMonth)'},Day={'<=$(vMaxDay)'},Date=,MonthYear=>}Sales)

and for prior YTD:

SUM({<Year={'$(vPriorYear)'},Month={'$(vMaxMonth)'},Day={'<=$(vMaxDay)'},Date=,MonthYear=>}Amount)

Re: Last month, last year function

tresB — Tue, 10 Sep 2013 12:01:13 GMT

Can you please share a sample app?

Re: Last month, last year function

Wed, 11 Sep 2013 07:26:14 GMT

Thank you all! Now it works

Here an example qvw, if someone need it.