topic Re: Get Current month and previous month in QlikView

Get Current month and previous month

abhijitnalekar — Mon, 15 Jun 2015 08:17:44 GMT

Hi All,

I need help...

I want to calculate current month and previous month. Eg. Suppose all data is selected then values show current month(May) data and previous month(April) data, and if user click on April month filter then variable need to show current month as April and previous month as March. Hope you understand the requirement.

Regards, Abhijit

Re: Get Current month and previous month

marcus_sommer — Mon, 15 Jun 2015 10:14:59 GMT

Max(month) respectively Max(month) - 1 will return the current and the prevoius month regarding to the selections.

- Marcus

Re: Get Current month and previous month

Mon, 15 Jun 2015 10:20:39 GMT

Use two expression one is for current month and previous month.

example: expression for current month: =sum({<Month={"=$(=Max(Month))"}>}Sales)

expression for previous month: =sum({<Month={"=$(=Max(Month)-1)"}>}Sales)

Re: Get Current month and previous month

daniel_kusiak — Mon, 15 Jun 2015 10:30:30 GMT

For previous month you can use:

Sum({$<date={'=MonthsEnd(1,(date),-1)'}, Sales)

Re: Get Current month and previous month

Mon, 15 Jun 2015 10:35:16 GMT

You can create variables

Curr: vCurrMon

Prev : vPrevMon

vCurrMon = =month(date(Month,'MMM'))

vPrevMon = date(AddMonths((date(Month,'MMM')),-1,MMM'),'MMM')

thanks

Re: Get Current month and previous month

abhijitnalekar — Mon, 15 Jun 2015 11:31:31 GMT

Thanks Marcus,

previous month formula is not working properly when we select January month.

what should i do if i want to show current month when we are showing all data (there is nothing filtered).

Regards,

Abhijit

Re: Get Current month and previous month

marcus_sommer — Mon, 15 Jun 2015 11:51:29 GMT

Yes this is right by turn of the year it won't work (so simply). For this you could use a MonthCounter within the Master-Calendar: Re: Howto make a variable to calculate quarters regarding year changes?

Another possibility is the use of addmonths() maybe in this way:

month(max(Date)) // current month

month(addmonths(max(Date), - 1)) // previous month

- Marcus

Re: Get Current month and previous month

Anonymous — Mon, 15 Jun 2015 12:35:48 GMT

Current month :sum({<Month={"=$(=Max(Month))"}>}Sales)

Previous month :sum({$<Month={"=$(=Max(Month)-1)"}>}Sales)

Re: Get Current month and previous month

Anonymous — Mon, 06 Jun 2016 09:11:39 GMT

Try this.

Current Year = Year(Max(Date))

Current Month = month(Max(Date))

Previous Year = =Year(addmonths(max(Date) ,- 1))

Previous Month = Month(addmonths(max(Date) ,- 1))

Re: Get Current month and previous month

Pulkit_Thukral — Mon, 06 Jun 2016 10:24:58 GMT

Current_Month={"$(=DATE(FLOOR(MonthStart(max(Date))),'MMM'))"}

Last_Month={"$(=DATE(FLOOR(MonthStart(AddMonths(max(Date),-1))),'MMM'))"}

Re: Get Current month and previous month

vatsalajmera5 — Wed, 24 Jan 2018 22:55:50 GMT

Hi,

The easiest solution to this would be to the monthAgo calculation in the Qlik Calender.

create an auto calendar for the date field you are trying to visualize by using the Derive fields function in the calendar script. ( I can attach the complete auto calendar script if needed)

ex:- DERIVE FIELDS FROM FIELDS [date Field]

now, for current month the code in the measure field would be :

Sum( {$ < [Date.autoCalendar.MonthsAgo] = {'0'}>} sales)

U can change the 0 to 1,2,3 to go back to n last months.

Hope this helps.

Re: Get Current month and previous month

sandeep_sarawgi — Tue, 09 Apr 2019 08:39:07 GMT

This is an old post, but if it helps anyone--consider adding this in your calendar script to identify current month (and year) records and prior month to that current month as 1 or else 0:

IF(MONTH(TempDate) = MONTH($(varMaxDate)) and
Year(TempDate) = Year($(varMaxDate)),1,0) AS CurrentMonthFlag,

IF(MONTH(TempDate) = MONTH(ADDMONTHS($(varMaxDate),-1))
  and if(Month(TempDate)=1,
           if(Year(TempDate)-1= Year($(varMaxDate))-1,1,0),
                    if(Year(TempDate)=Year($(varMaxDate)),1 ,0))=1,1,0)
                    AS PriorMonthFlag

Re: Get Current month and previous month

MMCGLAESASAA — Mon, 28 Mar 2022 12:21:33 GMT

Not working in Qlik sense