Skip to main content

Hello all,

I used this formular to get the average time of late payment:

AVG((DAYS_BETWEEN(PU_FIRST("BSEG", "_CEL_AR_ACTIVITIES"."EVENTTIME","_CEL_AR_ACTIVITIES"."ACTIVITY_EN" = 'Due Date passed'),"BSEG"."TS_AUGDT")) - <%= buffer1 %> )

 

This works well but when I filter on Fiscal Year the KPI is wrong because our Fiscal Year is not similar to Calendar Year. Our Fiscal Year starts always in october. How can I change my formular that the KPI is calculated for example from october 2024 til september 2025 when I selected 2025 as Fiscal Year.

 

Thanks and best regards.

Hey Julia,

 

A couple of angles we could look at here:

  1. How are you filtering on the fiscal year? What’s the formula (& component) you’re using there?
  2. Since you’re using a PU function on the date, you might want to consider adding the FILTER_TO_NULL function so the KPI can be filtered appropriately.

 

 

Let us know how it goes!

Hi Jin,

 

thank you for your reply. We take the component filter dropdown with the fiscal year (is a field directly from SAP) to select the necessary year. 

 

Regards

Hi Julia,

So I’m not too familiar with SAP - but I’m assuming you’re using the "BSEG"."GJAHR" column and my understanding is that those dates (or FYs) should be configured internally in the SAP system. I’d confirm if that column is configured correctly.

If its not, you might have to create the fiscal year filter manually using a combination of the CASE WHEN function + the date of the BSEG doc.

If they are configured correctly, could you elaborate a bit more on how the KPI is wrong after filtering on the FY?

Hi Jin, 

yes, we use BSEG.GJAHR. 

When I select Fiscal Year 2024 that means we want to see dates from 1st october 2023 till 30th september 2024 (because fiscal year is not calender year). But with this selection we only select the year that means, when I use the clearing date in a formular, than it will picked all dates with year 2024 (1st january 2024 till 31th december 2024). This would not be correct because we miss the information from 1st october 2023 till 31th december 2023 and the time from 1st october 2024 till 31th december 2024 is already part of fiscal year 2025. 

 

So I need a formular for a filter or something else that replace the year to year+1 when the date is in october, november or december.

 

Regards

Hey Julia,

 

if thats the case, then if I were you, I’d:

  1. Check with IT why the SAP system doesnt reflect your FY accurately.
  2. Insert the following PQL formula in your filter dropdown component as a workaround:

CASE

WHEN "BSEG"."BLDAT" BETWEEN {d '2025-10-01' } AND {d '2026-09-30' } THEN 'FY 2026'

WHEN "BSEG"."BLDAT" BETWEEN {d '2024-10-01' } AND {d '2025-09-30' } THEN 'FY 2025'

WHEN "BSEG"."BLDAT" BETWEEN {d '2023-10-01' } AND {d '2024-09-30' } THEN 'FY 2024'

ELSE 'Past FYs' END


 

Please keep in mind that I don’t know exactly which date you want to filter on or how far back you want to look, so please amend the formula appropriately.

Hope this helps!

Reply


Community Code of Conduct