topic Re: How to Get Prev Month Value in QlikView

How to Get Prev Month Value

Tue, 09 Jun 2015 12:06:15 GMT

Hi All,

I need to get Previous Month Value when i make a selection, but struggling to get it right?

E.g if i select May on a list box it must show me values for April.

like -1

i Tried:

=sum({<Dates={'>=$(=only(Dates)-2)<=$(=only(Dates))'}>}Sales)

and

=sum( {$<Year = {$(=Only(Year)-1)}>} Sales )

im i missing something in here?

Regards:

Thabiso

Re: How to Get Prev Month Value

avinashelite — Tue, 09 Jun 2015 12:10:50 GMT

try like this:

sum({<Month_Name={'>=$(monthname(monthname(date)-1)'}>}Sales)

and

=sum( {$<Year = {"$(=(Year-1)"}>} Sales )

Re: How to Get Prev Month Value

Tue, 09 Jun 2015 12:14:08 GMT

Hi,

What do you mean in Month_Name? and what should i use as a Dimension?

Re: How to Get Prev Month Value

sunny_talwar — Tue, 09 Jun 2015 12:15:39 GMT

Are you trying to do this on a chart where Month is your dimension? If yes than you will have to use Above() function. If not then this should work:

=Sum({<Dates={">=$(=Date(AddMonths(Max(Dates), -1), 'DatesFieldFormat')) <= $(=Date(Max(Dates)))"}>} Sales)

=Sum({<Dates={"$(='>=' & Date(AddMonths(Max(Dates), -1), 'DatesFieldFormat') & '<=' & Date(Max(Dates)))"}>} Sales)

Re: How to Get Prev Month Value

qlikmsg4u — Tue, 09 Jun 2015 12:16:24 GMT

That is why it is better to post sample app, to understand better by the community members. no offence.

Re: How to Get Prev Month Value

Tue, 09 Jun 2015 12:18:31 GMT

Hi All,

i want to use it on a Straight Table, so i just need sample Test so that i can Apply it on my Large Data Set, i created a Sample Model.

Regards.

Re: How to Get Prev Month Value

sunny_talwar — Tue, 09 Jun 2015 12:20:45 GMT

Can you share your sample with us? It will be easier to understand your structure and give you recommendations.

Best,

Sunny

Re: How to Get Prev Month Value

martin_dideriks — Tue, 09 Jun 2015 12:22:52 GMT

Do you just want the value for april when you select april?

Then it is:

sum({$<Year,Month,Date={">=$(=MonthStart(Min(Date),-1))<=$(=MonthEnd(Min(Date),-1))"}>} SalesAmount)

If you want to show the value for april next to may in a seperate column like this:

Month Sales Sales Last Month

April

May

Jun

Then i suggest that you take a look at this post, where I do somethng similar:

Re: Last Year and Current Year Sales in Current Year Only

//Martin

Re: How to Get Prev Month Value

Tue, 09 Jun 2015 12:26:28 GMT

Hi,

Sample Data:

Day	MonthDate	Dates	Year	Sales
10	Jan	2015-01-10	2015	100
24	Feb	2015-02-24	2015	23
24	Mar	2015-03-24	2015	45
10	May	2015-05-10	2015	345

Listbox:

MonthDate:

Jan

Feb

Mar

Apr

May

Jun

So if i select Mar it must show me values for Feb.....and if i select Feb it must show values for Jan.....and so on

Hope this will help

Thanks

Re: How to Get Prev Month Value

Tue, 09 Jun 2015 12:35:35 GMT

Hi,

Basically it has to calculate for Previous Month

e.g if i press Jun, it must give me sales for May

Thanks.

Re: How to Get Prev Month Value

Tue, 09 Jun 2015 12:37:33 GMT

Hi SunIndia, yours it gives me current month Selected thou i need Previous month from that selected Month.

if on my listbox i select March, it must calculate for February.

Thanks

Re: How to Get Prev Month Value

sunny_talwar — Tue, 09 Jun 2015 12:39:45 GMT

Try this expression:

=Sum({<Dates = {"$(='>=' & Date(AddMonths(MonthStart(Max(Dates)), -1), 'YYYY-MM-DD') & '<=' & Date(MonthEnd(Max(Dates)), 'YYYY-MM-DD'))"}, MonthDate = >}Sales)

Attaching the qvw for your review purposes.

Best,

Sunny