Qlik Community

Qlik Sense App Development

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

joey_lutes
Contributor

Compare Dates in Timeline

I'm attempting to compare records, by date, to the x-axis timeline date.

For example:

Dimension = ReferenceDate (common calendar date)

Measure:

For each reference date, I wish to:

if(ObjectAddedDate < ReferenceDate,  count(object.id))

Alternatively I assume this could be done with set analysis

count({$<ObjectAddedDate={"<$(=ReferenceDate)"}>}object.id)

I'm just not sure I'm going about this the right way or if this is possible.  Obviously it's not working yet

Thoughts?

Thanks in advance

5 Replies

Re: Compare Dates in Timeline

Can you give an example with data? What is the source data and what is the result table you expect based on the source table? Tables first, we'll do the line chart later.


talk is cheap, supply exceeds demand
MVP
MVP

Re: Compare Dates in Timeline

The set expression won't work, because it's evaluated once per chart, not per dimensional value.

You could try as expression:

=Count( if(ObjectAddedDate < ReferenceDate,  object.id))


(or maybe you need a Count(DISTINCT ...) )


but it's hard to suggest a solution, without knowing the context, including your data model and data.


You may also want to look into

Creating Reference Dates for Intervals

if you want to count objects with a added (and potentially removed) date over time. Removed or end date could be statically set to Today(), for example.

joey_lutes
Contributor

Re: Compare Dates in Timeline

I've gone about it a different way by using HICs canonical calendar / applymap method and use datetypes.  What I've done does seem to have done the trick there.

Dimension:

[$(vDim)Date] //canonical datefield set in load script

Measure:

count({$<[$(vDim)DateType] = {"$(vDim)Added"}>}[$(vDim).id])  //datetype created in loadscript

Using that method, I only have 1 issue:

For dates where nothing was added (are null), the chart(bar) does not populate it - even if I check 'show null values'

Is this normal behavior?

vdim.JPG

Here's a 'zoomed' image of my data model, and a 'zoomed out' version (don't be alarmed - there are 6 'entities', all with similar structure to the blown up model, creating many tables).  There is no 'master' canonical calendar (see Add Date to Incremental Load with multiple tables) - which is causing me no end of headaches, but I'm attempting to work around it.

You'll notice site.id has a link going off into space - it links to another disparate table.  I'm wondering if that might be causing this?  Perhaps I need to load site.id  then site.id as site-cal.id then link that one?

The goals for this are to show temporal movement of this network.  It's an incremental load, and the user wants to see everything, by load, adds, changes, deletes, timelines, etc - by any given moment in time.

Any help is truly appreciated.

modelzoom.JPG

model.JPG

joey_lutes
Contributor

Re: Compare Dates in Timeline

So here's an idea!

I have a table called 'ReferenceDate'.  It's a table of all dates, autogenerated, from min to max.  It's not linked to anything, just a table of dates with associated calendar.

Each entityalso has its own (entity)Date which is a common date between the different dates within that entity (Added, Verified, Removed) to which it is associated (see model).

What if I did this in the load:

Let vReferenceDate = ReferenceDate;

Let $(vDim)Date = $(vReferenceDate)

Would that work??  That way, it would tie the two together without creating the sythetic mayhem.  Thoughts?

joey_lutes
Contributor

Re: Compare Dates in Timeline

Interesting note:

Issue was only dates displaying on a bar graph where data exists, no other.

Here's what the same expressions produce in a line graph:

linegraph.JPG

The dimension is "siteDate"

Here's a look at the table:

sitedate.jpg

The 4 rows not displayed list 'siteRemoved' rows.

This makes no sense to me why the same expressions show different results in different graphs, and they either don't show enough dates, or too many. 

Thank you.