topic Re: Calculate CY QTD and Pevious QTD in App Development

Calculate CY QTD and Pevious QTD

Aspiring_Developer — Mon, 02 Dec 2024 15:38:00 GMT

Hello All ,

I have below data set and I want to calculate Current QTD and previous QTD in a single multi KPI . Here is my data set :-

In my Firt KPI , it should show the QTD for Oct 24 till Dec 24 and in second KPI , it should show the QTD for Aug-24 til sept 24 and it should be dynamic . I am trying below expression but can't do anything :-

Sum(
{<
transaction_dt = {">=$(=Date(MakeDate(If(Month(Today()) >= 4, Year(Today()), Year(Today()) - 1), 4, 1), 'MM/DD/YYYY')) <=$(=Date(Today(), 'MM/DD/YYYY'))"},

[Fiscal Quarter-Year] = {"$(If(Month(Today()) >= 4, Year(Today()), Year(Today()) - 1)) Q" & Ceil(Month(Today()) / 3)},

[Fiscal Year] = {"$(If(Month(Today()) >= 4, Year(Today()), Year(Today()) - 1))"},

[Month-Year] = {">=$(=Date(MakeDate(If(Month(Today()) >= 4, Year(Today()), Year(Today()) - 1), (Ceil(Month(Today()) / 3) - 1) * 3 + 1, 1), 'YYYY-MM-DD')) <=$(=Date(Today(), 'YYYY-MM-DD'))"}
>}
diesel_litres
)

Syntax error :-Error in set modifier ad hoc element list : ',' or ')' expected .

I am unable to rectify this @Kushal_Chawda

Re: Calculate CY QTD and Pevious QTD

Kushal_Chawda — Mon, 02 Dec 2024 16:35:40 GMT

@Aspiring_Developer If your fiscal year starts with Month October try below with Month number 10. You can change the month number according to your fiscal year setup

QTD - Sum( {< transaction_dt = {">=$(=QuarterStart(today(),0,10))<=$(=QuarterEnd(today(),0,10))"} >} diesel_litres ) PY QTD- Sum( {< transaction_dt = {">=$(=QuarterStart(addyears(today(),-1),0,10))<=$(=QuarterEnd(addyears(today(),-1),0,10))"} >} diesel_litres )

Re: Calculate CY QTD and Pevious QTD

Aspiring_Developer — Mon, 02 Dec 2024 16:51:33 GMT

@Kushal_Chawda Thank you but I want this to be dynamic .

Re: Calculate CY QTD and Pevious QTD

Kushal_Chawda — Mon, 02 Dec 2024 16:59:08 GMT

@Aspiring_Developer what do you mean by dynamic?

Re: Calculate CY QTD and Pevious QTD

Aspiring_Developer — Mon, 02 Dec 2024 17:22:46 GMT

@Kushal_Chawda Here The range is set as 10 which is explicitly for the month of October . When the data for January till march 2025 will come , then this KPI should show the data for the latest QTD , i.e for january 2025 . Similarly , previous QTD would become the sum of values starting from Oct 2024 till Dec 2024

Re: Calculate CY QTD and Pevious QTD

Kushal_Chawda — Mon, 02 Dec 2024 18:25:32 GMT

@Aspiring_Developer My expression is already dynamic. What I meant is that if you're trying to calculate QTD for a fiscal year starting in October, the QuarterStart function requires a parameter to specify that QTD begins in October, not January. That's why the parameter '10' was used. In your case, the following should work

QTD - Sum( {< transaction_dt = {">=$(=QuarterStart(today()))<=$(=QuarterEnd(today()))"} >} diesel_litres ) PY QTD- Sum( {< transaction_dt = {">=$(=QuarterStart(today(),-1))<=$(=QuarterEnd(today(),-1))"} >} diesel_litres )