Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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.
anat
Specialist II
Specialist II

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