Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Edit: Works also without Num(Floor
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
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.
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
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?
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
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:
Edit: Works also without Num(Floor
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
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
Hi David,
Thank you so much !
It worked for me.
Thanks,
Shubhangi