topic Re: Current Month vs Last Month in QlikView

Current Month vs Last Month

Mon, 15 Apr 2013 21:11:52 GMT

Folks,

I'm trying to build a table with 2 collumns: Current Month Sales | Past Month Sales.

Could you help me with the expression to create the Past Month Collumn ?

Thanks

Vinicius

Re: Current Month vs Last Month

MayilVahanan — Mon, 15 Apr 2013 22:03:32 GMT

Check this

http://community.qlik.com/message/325601#325601

Hope it helps

Re: Current Month vs Last Month

Tue, 16 Apr 2013 04:36:38 GMT

HI ,

For Past Month try this code :

if(Month(Date)=month(AddMonths(Today(),-1)), Sales ) as PastMonthSales

//Yusuf

Re: Current Month vs Last Month

neha_shirsath — Tue, 16 Apr 2013 05:09:13 GMT

Hi,

Try this Expression for last month data-

=sum({<Year={$(=max(Year))},Month={$(=month(addmonths((max(Date)),-1)))}>}Sales)

Regards,

Neha

Re: Current Month vs Last Month

qlikviewajeet — Tue, 16 Apr 2013 05:21:26 GMT

Hi, Vinicius

Create two variables

1. vCurrentMonth

=Max(Month)

and make ur expression like:

sum( {$<Year = {$(#vCurrentMonth)}>} Sales )

2. vLastMonth

=Max(Month-1)

sum( {$<Year = {$(#vLastMonth)}>} Sales )

Same as u can create Current Year and Last Year calculation

Regards

Ajeet Tomer

Re: Current Month vs Last Month

israrkhan — Tue, 16 Apr 2013 07:19:15 GMT

Hi,

Use =sum(Amount) for current month.

Use =sum({<Month={$(=Max(Month)-1)}>} Amount) for last Month.

it will work on your current selections:

Hope this help.

Kind Regards

Khan

Re: Current Month vs Last Month

rustyfishbones — Thu, 02 May 2013 21:22:00 GMT

Try

Current Month:

SUM({<DateField ={">=$(=MonthStart(Today())) <=$(=MonthEnd(Today()))"}>} Amount)

Previous Month:

SUM({<DateField ={">=$(=MonthStart(Today(),-1)) <=$(=MonthEnd(Today(),-1))"}>} Amount)

In this Example if the DateField was [Calendar Date] it would look like this

SUM({<[Calendar Date] ={">=$(=MonthStart(Today())) <=$(=MonthEnd(Today()))"}>} Amount)

SUM({<[Calendar Date] ={">=$(=MonthStart(Today(),-1)) <=$(=MonthEnd(Today(),-1))"}>} Amount)

Let me know if this works

Regards

Alan

Re: Current Month vs Last Month

Thu, 04 Aug 2016 00:37:46 GMT

I found the following to work more accurately for me.

=count ({<ClaimsAssisstantSubmittedDate ={">=$(=MonthStart(Today(),-1)) <$(=MonthStart(Today()))"}>} JobID)

The difference:

Alan's code has <= monthend(today,-1);

I have < monthstart(today)

Re: Current Month vs Last Month

vatsalajmera5 — Wed, 24 Jan 2018 22:54:23 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: Current Month vs Last Month

muratakkemik — Sat, 10 Feb 2018 16:43:04 GMT

could you please send me auto calendar full script?

thx

Murat

Re: Current Month vs Last Month

vatsalajmera — Mon, 12 Feb 2018 16:27:17 GMT

Hi,

Below is the query for the auto Calender. Remeber this is for QLIKSense. Load the script and use the Derive function to add the fields for which you want the auto calender. Let me know if it works for you.

[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;

DERIVE FIELDS FROM FIELDS [DATE FIELD] USING [autoCalendar] ;

Re: Current Month vs Last Month

RohanPR — Tue, 26 Feb 2019 09:06:18 GMT

Hi,

I tried the same expression but I got the value as 0. Any reason why this is happening