topic Compare Dates in Timeline in App Development

Compare Dates in Timeline

joey_lutes — Fri, 09 Dec 2016 05:18:09 GMT

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

Re: Compare Dates in Timeline

Gysbert_Wassenaar — Fri, 09 Dec 2016 08:22:20 GMT

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.

Re: Compare Dates in Timeline

swuehl — Fri, 09 Dec 2016 10:52:02 GMT

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.

Re: Compare Dates in Timeline

joey_lutes — Fri, 09 Dec 2016 23:16:33 GMT

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?

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.

Re: Compare Dates in Timeline

joey_lutes — Sat, 10 Dec 2016 18:20:44 GMT

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?

Re: Compare Dates in Timeline

joey_lutes — Sun, 11 Dec 2016 22:25:35 GMT

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:

The dimension is "siteDate"

Here's a look at the table:

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.