Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
jagan
Luminary Alumni
Luminary Alumni

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
petter
Partner - Champion III
Partner - Champion III

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>))

Anonymous
Not applicable
Author

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‌?:)

jagan
Luminary Alumni
Luminary Alumni

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.

Anonymous
Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.

Anonymous
Not applicable
Author

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:)