Forum Discussion

kramaswamy's avatar
kramaswamy
Frequent Visitor
5 years ago
Solved

Measure to analyze current date vs record dates

Hi all,

 

Trying to figure out how to construct a measure that will return true or false based on whether the date being analyzed occurs after a record date and before another date on the same record.

 

Specifically - I have a table which is joined to a date table against the record creation date. I want to know if the date I'm looking at in Excel / etc... (using a hierarchy of fiscal dates (year/quarter/month)) occurs after that creation date, but before the closed date of the record.

 

Please let me know if the question is unclear, as I'm having trouble trying to spell it out properly. Thanks!

5 Replies

  • kramaswamy's avatar
    kramaswamy
    Frequent Visitor
    5 years ago

    Hi there Jay,

     

    Not sure if this will help, but here's basically what I'm looking to do:

     

     

    That third column ("Number of Requests Closed") should basically be getting the count of records for each time block that were open at that particular time. My records have two dates - the date they were opened and the date they were closed. So - if I were looking at 01 Oct 2020, I would want to see a count of records that, as of 01 Oct 2020, were opened and not yet closed.

     

    If I had the following five records:

     

    RequestedOnClosedOn
    2020-10-13 0:002020-10-17 15:49
    2020-08-30 0:002020-11-17 0:00
    2020-03-21 0:002020-03-26 13:52
    2020-03-23 0:002020-04-06 13:02
    2020-03-28 0:002020-04-02 7:50

     

    For 01 Oct 2020, I would expect to see only one record show up for pending. For 14 Oct 2020, on the other hand, I would expect to see two records show up for pending.

     

    Does this make more sense?

  • kramaswamy's avatar
    kramaswamy
    Frequent Visitor
    5 years ago

    Actually - now that I think about it some more, it occurs to me that this information might not really even be possible, since it wouldn't really make any sense to roll that info up - IE, if in my previous example I wanted to see one record show up for 01 Oct 2020 and two for 14 Oct 2020, I would have to decide how the month of October should handle that info, and likewise, how it continues rolling up to higher levels.

     

    Is that something I can program as part of the measure? I know that I can use ISINSCOPE to decide which stat is being presented - I suppose I'd have to use a different stat to display depending on what is being analysed? IE, if I were looking at the daily data, I'd see what I had described in my post, whereas if I were looking at monthly/quarterly/annually, I would, for example, take the day with the highest value within that month/quarter/year?