DISTINCT COUNTING IN FILTERED DATA USING FORMULA

[aldixit]

I have requirement of counting distinct data in filter using formula

I want to count growth of payer I know following formula is wrong 

Please let me know any trick

(SUM(
IF(
LDM_SCHEMA.REGISTRATION.Reg_Date = $TDate,
Distinct(
CMO_REPORT.REGN.PAYER_NAME
),
0
)
) - sum(
IF(
LDM_SCHEMA.REGISTRATION.Reg_Date = addDays(
addYears(
$TDate,
-1
),
0
),
Distinct(
CMO_REPORT.REGN.PAYER_NAME
),
0
)
)) / sum(
IF(
LDM_SCHEMA.REGISTRATION.Reg_Date = addDays(
addYears(
$TDate,
-1
),
0
),
Distinct(
CMO_REPORT.REGN.PAYER_NAME
),
0
)
)

[shashidhar_sris]

Hi,

Please follow below steps what I have done.

1) For reference,  created a sample data for 2022 & 2023 registatrations.

2) created business schema after loading the data.

Formula used in Business schema:

Reg_2023:

• if( year( Shashi_Test.Registration2.Reg_Date ) = $currentYear, ( Shashi_Test.Registration2.Payer_Name ), "0" )

Reg_2022:

if( year( Shashi_Test.Registration2.Reg_Date ) = $lastYear, (

Shashi_Test.Registration2.Payer_Name ), "0" )

3) Created KPIs:

This will give 2022 vs 2023 growth

For your reference, attaching below month wise and day wise growth for 2022 vs 2023

 

Please let me know if you need more clarification.

Regards,

Shashidhar.S