topic Re: Comparing in a table previous year last quarter to current year first quarter in App Development

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

dana — Mon, 08 May 2023 05:44:29 GMT

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!

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

vinieme12 — Mon, 08 May 2023 08:07:41 GMT

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

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

dana — Mon, 08 May 2023 09:00:00 GMT

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..

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

vinieme12 — Mon, 08 May 2023 09:42:20 GMT

just remove the Year filter from set analysis

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

dana — Mon, 08 May 2023 14:53:26 GMT

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..