Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys
I need a set analysis that will pick up the last quarter?
I tried this one, but I think it doesn't work because when you do -1 on Quartername function it gives you the date in "47637" etc.
=sum({<[Quarter Name]={$(=QuarterName(today()))-1}>}[Sick Days Lost])
I've got
Year
Month
MonthYear
QuarterYear
MonthName
MonthNum
In a calendar.
Thanks
create an indicator in your calendar for the previous quarter
Year(DateField) & '-' & 'Q' & Ceil(Month(DateField)/3) AS CalendarYearAndQuarter
IF(quarterstart(DateField) = quarterstart(today(),-1),1,0) as PreviousQuarterIND,
Then for your set use: =sum({<[PreviousQuarterIND]={1}>}[Sick Days Lost])
If you have Quarter in the Q'n' format then you can use
'Q' & Ceil( Month(QuarterStart(max(Date), -1)) /3 )
or you can convert this expression to whichever format you want.
In a set analysis to sum something?
I Think that worked, let me check the numbers.
create an indicator in your calendar for the previous quarter
Year(DateField) & '-' & 'Q' & Ceil(Month(DateField)/3) AS CalendarYearAndQuarter
IF(quarterstart(DateField) = quarterstart(today(),-1),1,0) as PreviousQuarterIND,
Then for your set use: =sum({<[PreviousQuarterIND]={1}>}[Sick Days Lost])
That worked a treat, numbers match spot on!