5 Replies Latest reply: Dec 11, 2016 5:25 PM by Joey Lutes RSS

    Compare Dates in Timeline

    Joey Lutes

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

      For example:


      Dimension = ReferenceDate (common calendar date)



      For each reference date, I wish to:

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


      Alternatively I assume this could be done with set analysis



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


      Thanks in advance

        • Re: Compare Dates in Timeline
          Gysbert Wassenaar

          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
            Stefan Wühl

            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

                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.



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


                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

                    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

                        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.