Comparing in a table previous year last quarter to current year first quarter

[dana]

Hi People,

I am trying to compare quarters for a current (selected) year. 

I need to display difference between consecutive rows.

I wanted to display only the current years quarters, and for the 1st quarter, to display the difference from the last quarter in the previous year. 

Based on previous posts, I tried the following expression:

if(RowNo() =2,
(Sum({<Year = {'$(vCurrentYear)'},Date={"<=$(vMaxDate)"}>} CountEntries_Monthly)
/
Sum({< $(eClearDates),QuarterStart = {"$(=QuarterStart(vMinDate,-1))"} >} CountEntries_Monthly)
)
-1
,
If(RowNo()>2,
(
Sum({<Year = {'$(vCurrentYear)'},Date={"<=$(vMaxDate)"}>} CountEntries_Monthly)
/
Above(
Sum({<Year = {'$(vCurrentYear)'},Date={"<=$(vMaxDate)"}>} CountEntries_Monthly)
))
-1
))

But it doesn't work..

I also don't want 2021-Q4 to be displayed..  

Can someone helpme?

Thanks!

[vinieme12]

Qty

= Sum({<Year = {'$(vCurrentYear)'},Date={"<=$(vMaxDate)"}>} CountEntries_Monthly)

Difference

= Sum({<Year = {'$(vCurrentYear)'},Date={"<=$(vMaxDate)"}>} CountEntries_Monthly) - Above(Sum({<Year = {'$(vCurrentYear)'},Date={"<=$(vMaxDate)"}>} CountEntries_Monthly) )

%Difference

= 1 - (Sum({<Year = {'$(vCurrentYear)'},Date={"<=$(vMaxDate)"}>} CountEntries_Monthly) / Above(Sum({<Year = {'$(vCurrentYear)'},Date={"<=$(vMaxDate)"}>} CountEntries_Monthly) ) )

[dana]

Hi,

Your suggestion would not display the difference between the last  quarter in the previous year

and the first quarter in the current year.

Thanks..

[vinieme12]

just remove the Year filter from set analysis

[dana · Accepted solution]

Hi,

I figured a solution that satisfies partially, but it's simpler and works..

Added to Calendar:
QuarterStart(Date) AS QuarterStart

Added Variables:
vMinQuarterStart -  previous qtr to the first qtr in the selected period
=QuarterStart(vMinDate,-1)

vMaxQuarterStart - last qtr in the selected period
=QuarterStart(vMaxDate,0)

Qty
Sum({<$(eClearDates),QuarterStart = {">=$(vMinQuarterStart) <=$(vMaxQuarterStart)"}>} CountEntries_Monthly)

Difference
(
Sum({<$(eClearDates),QuarterStart = {">=$(vMinQuarterStart) <=$(vMaxQuarterStart)"}>} CountEntries_Monthly)
/
Above(
Sum({<$(eClearDates),QuarterStart = {">=$(vMinQuarterStart) <=$(vMaxQuarterStart)"}>} CountEntries_Monthly)
)
)
-1

Last year qtr is displayed, but i can live that ... 🙂

Appreciate any comments..