topic Re: Previous Quarter calculation in App Development

Previous Quarter calculation

snancyiv — Wed, 07 Sep 2022 13:56:50 GMT

Hi everyone!

I'm trying to calculate the change in two quarters, I think I'm getting close but not sure what else I am missing in my formula and hoping that someone can help. Basically, user will select a quarter and the KPI will show current value and what the change was from prior quarter.

Here are the sales figures:

Current Quarter Sales: 356

Previous Quarter Sales: 524

I created two variables to return the start dates, one for current quarter and another for previous quarter:

vCurrentQuarter: date(min([Year Month]),'YYYY-MMM')

vPreviousQuarter: date(addmonths(min([Year Month]),-3),'YYYY-MMM')

Calculation for Previous quarter sales: sum(${<'$(vCurrentQuarter)' ={"$(vPreviousQuarter)"}>} Sales)

The calculation keeps giving me current quarter sales. What am I missing? Thanks in advance!

Re: Previous Quarter calculation

BrunPierre — Wed, 07 Sep 2022 15:17:11 GMT

@snancyiv Once you have a well-formatted date field, try these

Current: Sum({<[Date Field]={">=$(=Quarterstart(today(1)))<=$(=Quarterend(today(1)))"}>}Sales)

Previous: Sum({<[Date Field]={">=$(=Quarterstart(today(1),-1))<=$(=Quarterend(today(1),-1))"}>}Sales)

Re: Previous Quarter calculation

snancyiv — Thu, 08 Sep 2022 15:38:06 GMT

Hi @BrunPierre ,

I'm still getting current quarter values. For more reference, the filter to to select quarters is based on the quarter start date, "QuarterStart" which is calculated field in the script as Quarterstart(Date).

Re: Previous Quarter calculation

BrunPierre — Thu, 08 Sep 2022 20:01:50 GMT

Provide some samples of the data.

Re: Previous Quarter calculation

snancyiv — Fri, 09 Sep 2022 13:16:43 GMT

Removed sensitive info from the original. Please filter "YearQuarter" on 2022-Q4 when you open it to see my example.

Re: Previous Quarter calculation

snancyiv — Mon, 12 Sep 2022 13:45:03 GMT

The association is on Date to Discharge Date. When you filter on 2022-Q4, it should be:

Current Quarter TotalNumerCase: 107

Previous Quarter TotalNumerCase: 162

Re: Previous Quarter calculation

BrunPierre — Tue, 13 Sep 2022 07:35:22 GMT

Maybe this

Current Quarter: Sum({<DischargeDate={">=$(=QuarterStart(Max(DischargeDate),-1))<=$(=AddMonths(Max(DischargeDate),-3))"}>}TotalNumerCase)

Previous Quarter:Sum({<DischargeDate={">=$(=QuarterStart(Max(DischargeDate)))<=$(=AddMonths(Max(DischargeDate),0))"}>}TotalNumerCase)

Re: Previous Quarter calculation

krishna_p — Tue, 13 Sep 2022 07:14:13 GMT

Hi,

Current Quarter : sum({<[Date-DischargeDate]={">=$(=QuarterStart(Max([Date-DischargeDate]))) <=$(=QuarterEnd(max([Date-DischargeDate])))"}>}TotalNumerCase)

Previous Quarter : sum({<[Date-DischargeDate]={">=$(=QuarterStart(Max([Date-DischargeDate]),-1)) <=$(=QuarterEnd(max([Date-DischargeDate]),-1))"}>}TotalNumerCase)

Thanks,

Krishna

Re: Previous Quarter calculation

snancyiv — Tue, 13 Sep 2022 12:39:04 GMT

I still get the same amount as current quarter for previous calculation. 😞

Re: Previous Quarter calculation

snancyiv — Tue, 13 Sep 2022 12:42:33 GMT

Still getting the same amount as current quarter

Re: Previous Quarter calculation

krishna_p — Wed, 14 Sep 2022 06:23:06 GMT

Hi,

don't wry, plz provide me data set files

so, i will calculate accurate answer for you and tell me one thing you have created variables in front-end or back-end.

Here are the sales figures which you want , right ?

Current Quarter Sales: 356

Previous Quarter Sales: 524

Re: Previous Quarter calculation

snancyiv — Wed, 14 Sep 2022 12:31:28 GMT

Hi,

data sets attached. The association is on Date to Discharge Date. No variables here. I do have a custom field for quarter:

[Fiscal Year] &'-'&
IF([Fiscal Month]='1','Q1',
IF([Fiscal Month]='2','Q1',
IF([Fiscal Month]='3','Q1',
IF([Fiscal Month]='4','Q2',
IF([Fiscal Month]='5','Q2',
IF([Fiscal Month]='6','Q2',
IF([Fiscal Month]='7','Q3',
IF([Fiscal Month]='8','Q3',
IF([Fiscal Month]='9','Q3',
'Q4'))))))))) AS [YearQuarter]

When you filter on 2022-Q4, it should be:

Current Quarter TotalNumerCase: 107

Previous Quarter TotalNumerCase: 162