Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
mslottje
Contributor II

QTD previous year

Hi guys,

I'm using the following calendar for my analysis.

SUB CALENDAR

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

  

LET varMinDate = num('1-1-2009');

LET varMaxDate = num('31-12-2017');   

TempCalendar: 

LOAD

$(varMinDate)+Iterno()-1 AS Num,

Date(($(varMinDate))+Iterno()-1) AS TempDate

AUTOGENERATE 1 WHILE $(varMinDate)+Iterno()-1<= $(varMaxDate);

MasterCalendar: 

Load 

               floor(date(TempDate,'DD-MM-YYYY')) AS %key_Date,

               TempDate AS Date,

               week(TempDate) As Week, 

               Year(TempDate) As Year, 

               Month(TempDate) As Month, 

               Day(TempDate) As Day, 

               //YeartoDate(TempDate)*-1 as CurYTDFlag, 

               //YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

               WeekDay(TempDate) as WeekDay,

               DayNumberOfYear(TempDate) as DayoftheYear,

               If( DayNumberOfYear(TempDate) <= DayNumberOfYear(Today()), 'Y', 'N' ) as IsInYTD,

               If( DayNumberOfQuarter(TempDate) <= DayNumberOfQuarter(Today()), 'Y', 'N') as IsInQTD,

      If( Day(TempDate) <= Day(Today()), 'Y', 'N') as IsInMTD,

      If( Month(TempDate) = Month(Today()), 'Y', 'N') as IsCurrentMonth,

      If( Month(AddMonths(TempDate,1)) = Month(Today()), 'Y', 'N') as IsLastMonth

Resident TempCalendar 

Order By TempDate ASC;

Drop Table TempCalendar; 

END SUB;

To compare our sales from current month current year vs current month previous year, I used the following script:

SUM(Date ={">=$(=date(addmonths(MonthStart(Today()),-12))) <=$(=date(addmonths(MonthEnd(Today()),-12)))"}[Nett Amount])

Now I want to do the same with quarters instead of months, but the function addquarters does not exist.

Could you guys explain me how to do this comparison?

Thanks a lot in advance!

Mike

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: QTD previous year

Hi,

Try this

SUM(Month=, Quarter=, Year=, Date ={">=$(=date(QuarterStart(Today(),-4))) <=$(=date(QuarterEnd(Today(),-4)))"}[Nett Amount])


Month=, Quarter=, Year=,  this will ignore the selections in those fields, since we need to get previous year data we have ignore the current selections of the date filters.



Hope this helps you.


Regards,

jagan.

View solution in original post

6 Replies
MVP
MVP

Re: QTD previous year

My quick testing makes me believe an expression like this will give you a starting ground for calculating AddQuarters:

QuarterStart(AddMonths(QuarterStart(Today()),3*<number-of-quarters>))

QuarterEnd(AddMonths(QuarterEnd(Today()),3*<number-of-quarters>))

mslottje
Contributor II

Re: QTD previous year

Clever thinking! Unfortunately this expression shows a null value when using it in my chart..

I tried

QuarterStart(AddMonths(QuarterStart(Today()),3*<4>))

QuarterEnd(AddMonths(QuarterEnd(Today()),3*<4>))

and

QuarterStart(AddMonths(QuarterStart(Today()),3*4))

QuarterEnd(AddMonths(QuarterEnd(Today()),3*4))

Any idea petter-s‌?:)

MVP & Luminary
MVP & Luminary

Re: QTD previous year

Hi,

Try this

SUM(Month=, Quarter=, Year=, Date ={">=$(=date(QuarterStart(Today(),-4))) <=$(=date(QuarterEnd(Today(),-4)))"}[Nett Amount])


Month=, Quarter=, Year=,  this will ignore the selections in those fields, since we need to get previous year data we have ignore the current selections of the date filters.



Hope this helps you.


Regards,

jagan.

View solution in original post

mslottje
Contributor II

Re: QTD previous year

Hi Jagan,

thanks for your reply! I understand what you're doing, but the chart gives an error for this reference line..

What should I do to figure out what this error is?

Kind regards,
Mike

MVP & Luminary
MVP & Luminary

Re: QTD previous year

Can you attach a screenshot of the error?  Check whether Month=, Quarter=, Year=, this columns exists in your datamodel if not remove it or rename it.

The expression is correct.

Check this links

Set Analysis for certain Point in Time

Set Analysis for Rolling Periods

Hope this helps you.

Regards,

Jagan.

mslottje
Contributor II

Re: QTD previous year

Thanks Jagan, I checked the expression again and there failed a '}' in the end of my edited expression of your version.

Works perfect now! Thanks a lot:)