Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Shubhangi_01
Contributor III
Contributor III

Need to compare two dates columns and take the count of matched data only to show in graph

Hello All, I have two columns of Initial launch Date and Actual launch date of all the projects . My requirement is that, I have to show number of projects launched on the same date which was initially decided. I need to compare these two columns and need to find if both the values are same. If yes, then need to show the count of all projects with respect to the months in graph. Can you please help me, and let me know how to compare this two columns and take the count of matched data only. Thanks in advance, Shubhangi
2 Solutions

Accepted Solutions
DavidM
Partner - Creator II
Partner - Creator II

Hi Shubhangi,

your condition is needlessly complicated. I suggest removing '-' in Load script. If that is not an option, there is simpler way to do this:

if(
        if(len([Actual Launch Date])<2,0,  // This condition disregard dates which have no actual launch date - I suggest using actual date as projects can have Initial, but miss actual date. 
             num(floor([Initial Launch Date]))) = num(floor(([Actual Launch Date]))), //The actual condition, works for me when importing from Excel
            count(distinct([Project Name])),0)

My chart using above looks like this:

 

clipboard_image_0.png

Edit: Works also without Num(Floor

 

 

 

View solution in original post

DavidM
Partner - Creator II
Partner - Creator II

Hi Shubhangi,

if you use month, you will get values Jan to Dec without Year, so if you will have more years in your data, you will show Jan data as summary of more years  - I suppose this is incorrect.

 

If your data is interpreted in Qlik as $date $timestamp (you can check this in data model viewer) you can use more functions, for example:

MonthStart("Initial Launch Date") - Which converts each date to the beginning of the month

Date("Initial Launch Date",'MM-YYYY') - which shows the values in format you specify (in this case MM-YYYY which is for eg 01-2019)

 

Hope this helps

 

View solution in original post

8 Replies
DavidM
Partner - Creator II
Partner - Creator II

Hello,

you can do it like this:

If( Initial date] = [Actual date],
count(distinct([Project])),0)

This functions returns distinct count of projects which have Initial Date equal to Actual launch date (all other zero). You can then uncheck the Include zero values box in Add-ons of the table and this will show you only projects, which started on time.

 

Shubhangi_01
Contributor III
Contributor III
Author

Hi David,

Thank you for your quick reply.

I tried the solution which you shared, but I am not getting any data in graph.
I have added Months field in my Dimension (X axis) and 

If ([Initial Launch date] = [Actual Launch date], Count(Distinct([Project_SysID])),0)

added in Expressions (Y axis).

Is there anything which I am doing wrong in this ?

 

Thanks,

Shubhangi

DavidM
Partner - Creator II
Partner - Creator II

Hi Shubhangi,

the method you have used should be fine (it works on my sample data), the problem might be elsewhere - e.g. your data fields have different format date - you might need to use Num, Floor (maybe even Date#) to get the right format. Would you mind sharing your data?

Shubhangi_01
Contributor III
Contributor III
Author

Hi David,

I am attaching the sample file for your reference. 

I want to exclude '-' value from my data. So I am trying to remove them in my expression. But it seems that is also not working.

Following is the expression i tried : 

If(count(Month([Initial Launch Date])) = Count(Month([Actual Launch Date]))
and count(Date#([Initial Launch Date])) = Count(Date#([Actual Launch Date]))
and [Initial Launch Date] <> '-' and [Actual Launch Date] <> '-',count(distinct([Project Name])),0)

Please let me know if I need to change anything in this expression.

Thanks in advance,

Shubhangi

DavidM
Partner - Creator II
Partner - Creator II

Hi Shubhangi,

your condition is needlessly complicated. I suggest removing '-' in Load script. If that is not an option, there is simpler way to do this:

if(
        if(len([Actual Launch Date])<2,0,  // This condition disregard dates which have no actual launch date - I suggest using actual date as projects can have Initial, but miss actual date. 
             num(floor([Initial Launch Date]))) = num(floor(([Actual Launch Date]))), //The actual condition, works for me when importing from Excel
            count(distinct([Project Name])),0)

My chart using above looks like this:

 

clipboard_image_0.png

Edit: Works also without Num(Floor

 

 

 

Shubhangi_01
Contributor III
Contributor III
Author

Hi David,

 

Thank you for your help. I got the same graph as of yours with the solution you provided.

But I want this data with respect to month and not each day.

Can you please let me know instead of Initial launch year, what can I use in Dimensions for X axis.

I tried doing :

Month (Initial launch Year) as Month

in Load script, and using the same in Dimension. But it is not giving me the data in graph.

 

Thanks,

Shubhangi

DavidM
Partner - Creator II
Partner - Creator II

Hi Shubhangi,

if you use month, you will get values Jan to Dec without Year, so if you will have more years in your data, you will show Jan data as summary of more years  - I suppose this is incorrect.

 

If your data is interpreted in Qlik as $date $timestamp (you can check this in data model viewer) you can use more functions, for example:

MonthStart("Initial Launch Date") - Which converts each date to the beginning of the month

Date("Initial Launch Date",'MM-YYYY') - which shows the values in format you specify (in this case MM-YYYY which is for eg 01-2019)

 

Hope this helps

 

Shubhangi_01
Contributor III
Contributor III
Author

Hi David,

Thank you so much !

It worked for me.

Thanks,

Shubhangi