4 Replies Latest reply: Oct 9, 2017 3:40 AM by Mike Slottje

# Previous quarter - set analysis expression

Hi experts,

In a dashboard, I want to show our sales in the previous quarter for current year with the following expression:

Sum({<Date={">=\$(=QuarterStart(Today())-1) <=\$(=QuarterEnd(Today())-1)"}>}[Nett Amount new EUR])

Unfortunately this expression doesn't work.

I tried this expression, because this works perfectly for the previous month when using the same expression with MonthStart en MontEnd instead.

Could you guys help me out to find an expression that works for the previous quarter?

Cheers,

Mike

PS.

I'm using the following script as master calendar.

QuartersMap:

rowno() as Month,

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

AUTOGENERATE (12);

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

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

TempCalendar:

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

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

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

MasterCalendar:

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,

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

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;

• ###### Re: Previous quarter - set analysis expression

what's the problem with your expression?

• ###### Re: Previous quarter - set analysis expression

It shows 0 as result, where there should be a number..

• ###### Re: Previous quarter - set analysis expression

May be

QuarterStart(Today(),-1)

QuarterEnd(Today(),-1)

• ###### Re: Previous quarter - set analysis expression

Haha wow, that was a stupid mistake.. Time for coffee I think

Thanks a lot Antonio!

For our fellow Qlik Sense users who are trying to show a previous quarter:

The old, not working, expression:

Sum({<Date={">=\$(=QuarterStart(Today())-1) <=\$(=QuarterEnd(Today())-1)"}>}[Nett Amount new EUR])