DISTINCT COUNTING IN FILTERED DATA USING FORMULA

aldixit — Wed, 30 Mar 2022 14:19:03 GMT

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

Re: DISTINCT COUNTING IN FILTERED DATA USING FORMULA

Shashidhar — Thu, 04 May 2023 17:28:13 GMT

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

topic Re: DISTINCT COUNTING IN FILTERED DATA USING FORMULA in Dashboards & Analytics Discussions

DISTINCT COUNTING IN FILTERED DATA USING FORMULA

Re: DISTINCT COUNTING IN FILTERED DATA USING FORMULA