topic Re: How to get Previous Month in QlikView

How to get Previous Month

Wed, 27 May 2015 05:51:39 GMT

Hi Guyys, i want to Find Out Previous Month Method. Suppose I have a One Year 2015 and 2014.. In that I want to

Compare the sum of sales month wise.. Like If I select 2015 as a Year and dec as a Month. so i get the Sum of sales

for Dec and Nov . as well if i select Year 2015 and Month Jan i get the sum of sales for Jan and Dec2014..

Kindly Help me ..

Re: How to get Previous Month

amit_saini — Wed, 27 May 2015 05:54:08 GMT

Hi,

Like this???

Thanks,

Re: How to get Previous Month

Wed, 27 May 2015 06:05:00 GMT

hi Amit..

This is not i want.. I want only on Month Basis.. JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ..

say i have a List Box for Month.. if i select MAR...I GET Value for MAR And FEB

Re: How to get Previous Month

amit_saini — Wed, 27 May 2015 06:09:10 GMT

Hi,

Try this:

=Date(MonthStart(Max(DateField), -1), 'MMM')

Thanks,

Re: How to get Previous Month

pradnyat — Wed, 27 May 2015 06:24:34 GMT

Hi,

Try below expression, hope this will solve your issue

=Date((Datefield)-1,'MMM-YY')

Re: How to get Previous Month

jonathandienst — Wed, 27 May 2015 06:35:10 GMT

The simplest way to perform this sort of analysis is to have a month sequence number in the master calendar

Calendar:

LOAD ....

Date,

Year(Date) As Year,

Month(Date) As Month,

Year(Date) * 12 + Month(Date) As MonthSeq,

...

Now you can use the expressions for selected month and the previous month:

Sum({<MonthSeq = {"$(=Max(MonthSeq))"}>} Sales)

Sum({<MonthSeq = {"$(=Max(MonthSeq) - 1)"}>} Sales)

You may need to override selections of Month and Year....

Sum({<MonthSeq = {"$(=Max(MonthSeq))"}, Month, Year>} Sales)

Sum({<MonthSeq = {"$(=Max(MonthSeq) - 1)"}, Month, Year>} Sales)

See

Understanding the Master Calendar (video)

Calculating rolling n-period totals, averages or other aggregations

Re: How to get Previous Month

Wed, 27 May 2015 06:38:53 GMT

Thanks Amit,

Its Working Fine.. But When i select JAN2015 its Shows the Value for DEC2015.. Can we show DEC2015 Value..

Thanks Once Again

Re: How to get Previous Month

senpradip007 — Wed, 27 May 2015 06:41:02 GMT

PFA. Hope it is you are looking for.

Re: How to get Previous Month

timanshu — Wed, 27 May 2015 07:05:42 GMT

You need to have date field also. Create it if does not exist in model.

In Script add , Date(makedate(Year,month),'MMYYYY') as Date.

then

Sum({<Date= {'<=$(=max(Date))>=$(=Date(addmonths(Date,-1),'MMYYYY'))'}>}Sales).

Re: How to get Previous Month

Wed, 27 May 2015 07:18:16 GMT

i m trying this Way for Previous Month..

=if(Month= 'Jan',sum({<Month = {$(vPreviousMonth)}, Year =(Max(Year)-1)>}Price_Pcs_Incl_Discount),sum({<Month = {$(vPreviousMonth)}>}Price_Pcs_Incl_Discount)).. its not showing any thing..

Re: How to get Previous Month

Wed, 27 May 2015 07:34:50 GMT

Try this expression if you have date field in your data model

Month=Month(Addmonths(MakeDate(Year(max(Date)),Month(max(Date)),Day(max(Date))),-1))

in set expression while calculating Amount

Re: How to get Previous Month

timanshu — Wed, 27 May 2015 08:30:15 GMT

Hi,

Change expression to :

=if(Month= 'Jan',sum({<Month = {'Dec'}, Year ={'$(=Max(Year)-1)'}>} Price_Pcs_Incl_Discount),sum({<Month = {'$(=vPreviousMonth)'},Year ={'$(=Max(Year))'}>}>}Price_Pcs_Incl_Discount)).

If still doesn't work, then kindly attach qvw .

Re: How to get Previous Month

timanshu — Wed, 27 May 2015 08:34:35 GMT

Hi,

It will not change year if max month is jan.

Re: How to get Previous Month

Wed, 27 May 2015 09:04:53 GMT

=month(monthstart(today())-1)

Re: How to get Previous Month

Wed, 27 May 2015 09:33:14 GMT

Use following Expression

You need to create Period field Period= Year&Month in script

then use in set expression Year=,Month=,Period = {$(=vPeriod)}

Where vPeriod is variable

vPeriod =Floor(max( if(mod(Period, 100) = 1, Period - 89, Period - 1)))

Regards,

Veeki

Plz Check App also