Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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.
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?
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.