cancel
Showing results for 
Search instead for 
Did you mean: 

DISTINCT COUNTING IN FILTERED DATA USING FORMULA

aldixit
Rocketeer

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

1 REPLY 1

shashidhar_sris
Partner
Partner

Hi,

Please follow below steps what I have done.

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

shashidhar_sris_0-1683220016425.png

2) created business schema after loading the data.

shashidhar_sris_2-1683220339599.png

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:

shashidhar_sris_1-1683220184602.png

This will give 2022 vs 2023 growth

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

 shashidhar_sris_4-1683220759698.pngshashidhar_sris_5-1683220849723.png

Please let me know if you need more clarification.

Regards,

Shashidhar.S