Comparison of different dates with one date axis

[Shubham_Deshmukh]

Hi,
I have requirement of showing year-wise count where I have 2 dates,

Effective_Date
Relief_Date

Dimension  : Year(Effective_Date)
Expression Logic : I want count(distinct IDs) where year(Relief_Date) >year(Effective_Date) 

Sample Data:

ID	Effective_Date	Relief_Date
1	04-Feb-20	30-Jul-21
2	04-Feb-20	30-Jul-21
3	04-Feb-20	30-Jul-21
4	07-Feb-20	08-Feb-21
5	07-Feb-20	30-Jul-21
6	13-Feb-20	30-Jul-21
7	15-Feb-20	30-Jul-21
8	17-Mar-20	30-Jul-21
9	18-Mar-20	30-Jul-21
10	24-Mar-20	30-Jul-21
11	24-Mar-20	30-Jul-21

Regards,
Shubham

[PrashantSangle]

Did you try any expression? if yes, then post it.

try below

dimension > Year(Effective_Date)

Measure > Count(DISTINCT if(year(Relief_Date) >year(Effective_Date),ID))

Regards,

Prashant Sangle

[anat]

can u try to create flag in backend script like 

if(year1 > year2,1,0) as flag

use this flag in set analysis

[Shubham_Deshmukh]

Hi Prashant - Yes, I tried the same expression as well but still count is not matching. I am not sure whether canonical calendar will be needed here or not.
Because same ID has 2 different dates, 1 is getting used for Axis and 2nd for comparison.

[PrashantSangle]

Hi Shubham,

Can you share some sample data with expected output?

Regards,

Prashant Sangle

[Shubham_Deshmukh]

Hi Prashant, 
Below is sample data, problem I identified is because of past and future  effective_dates(highlighted in red) which are not coming at 2020 in chart because they have 2019 & 2021 effective dates. 
Want to consider red highlighted as under 2020 as relief to date is >2020

Required logic : I want count(distinct IDs) where year(Relief_Date) >year(Effective_Date)  and relief_date is blank
My Expression : 

Count(distinct if(
CadReliefYear > CadEffectiveYear ,
[ID]))
+
Count(Total distinct if(
isnull([Relief_Date]) or
[Relief_Date]='',
[ID]))

Sample data : 

ID	Effective_Date	Relief_Date
1	30-Jun-19	30-Jul-21
2	09-Jul-19	1-Jun-21
3	30-Jun-20	30-Jul-21
4	09-Jul-20	30-Jul-21
5	27-Jul-20	30-Jul-21
6	30-Jul-21
7	30-Jul-21
8	30-Jul-21	07-Dec-21
9	30-Jul-21	08-Dec-21

Expected o/p :

	Year	Count
Expected	2020	9
Current result	2020	5 (green)

[PrashantSangle]

Need more clarity on requirement. Like if 

Scenario: 

ID: 10

Effective Date - 30 jun 19 and 

Relief Date - 30 Jul 25

Then It should come under which year 20?, 21?, 22?, 23? or 24?

Regards,

Prashant Sangle