Previous quarter - set analysis expression

[Anonymous]

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;

[agigliotti]

what's the problem with your expression?

[Anonymous]

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

[antoniotiman · Accepted solution]

May be

QuarterStart(Today(),-1)

QuarterEnd(Today(),-1)

[Anonymous]

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