Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mslottje
Contributor II

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: 

MAPPING LOAD  

rowno() as Month, 

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

AUTOGENERATE (12); 

  

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

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

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,

               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;

1 Solution

Accepted Solutions
antoniotiman
Honored Contributor III

Re: Previous quarter - set analysis expression

May be

QuarterStart(Today(),-1)

QuarterEnd(Today(),-1)

4 Replies
agigliotti
Honored Contributor II

Re: Previous quarter - set analysis expression

what's the problem with your expression?

mslottje
Contributor II

Re: Previous quarter - set analysis expression

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

antoniotiman
Honored Contributor III

Re: Previous quarter - set analysis expression

May be

QuarterStart(Today(),-1)

QuarterEnd(Today(),-1)

mslottje
Contributor II

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


The adjusted and working expression:

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