Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Shubham_Deshmukh
Specialist
Specialist

Comparison of different dates with one date axis

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

Shubham_Deshmukh_0-1644385097998.png

 


Regards,
Shubham

6 Replies
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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
anat
Master
Master

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
Specialist
Specialist
Author

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Shubham_Deshmukh
Specialist
Specialist
Author

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂