topic Comparison of different dates with one date axis in App Development

Comparison of different dates with one date axis

Shubham_Deshmukh — Wed, 09 Feb 2022 05:38:33 GMT

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

Re: Comparison of different dates with one date axis

PrashantSangle — Wed, 09 Feb 2022 07:08:11 GMT

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

Re: Comparison of different dates with one date axis

anat — Wed, 09 Feb 2022 07:26:35 GMT

can u try to create flag in backend script like

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

use this flag in set analysis

Re: Comparison of different dates with one date axis

Shubham_Deshmukh — Wed, 09 Feb 2022 10:02:27 GMT

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.

Re: Comparison of different dates with one date axis

PrashantSangle — Thu, 10 Feb 2022 03:26:01 GMT

Hi Shubham,

Can you share some sample data with expected output?

Regards,

Prashant Sangle

Re: Comparison of different dates with one date axis

Shubham_Deshmukh — Tue, 15 Feb 2022 06:12:34 GMT

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)

Re: Comparison of different dates with one date axis

PrashantSangle — Sun, 20 Feb 2022 05:24:00 GMT

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