Trying to get Point In Time data

[squeakie_pig]

Hi

I am trying to get my head around point in time data.  Attached is a sample.

I have a few tables all of which have effective dates.  I want to be able to choose a date in the dashboard (not load script) and all the different effective dated fields will filter by that date.

I have been playing around with different set expressions but can't seem to get the right combination of > and < .  Something like this works.

Count({< CUST_EFF_DATE = {'>=$(=Date(vDate1))'}>} distinct CUSTOMER_ID)

but I'm not sure of the syntax that would be needed to get all the different effective dates as at the selected date to recreate a record, eg. If I selected 08/01/2014, I would want it to show this record.

For anyone out there who is using the personal edition.  Load script is also attached as a text file.  Here is a screenshot of what is on the tab and the 'Point in time query' chart uses the expression above.

[sbaldwin]

Hi, your question is not totally clear, but i guess you want to be able to select a date (or range of dates?) as such that all the orders within this selection are displayed then show the correct customer name and address for these orders. effectively you have a fact table (orders) and two type-2 slowly changing dimension tables, so what you are missing is the valid_to_date in your data once this is populated (you could derive from the existing data) connect the calender table to your order table on the order date and apply set conditions on your customer, however the problem would be if you had a range of orders, which spanned the validity ranges, so you should really amend your script to join the dimension tables to the fact, using the extended syntax of interval match?

Thanks

Steve

[squeakie_pig]

Thank you Steve.

I had originally built up a data cube using interval match and various applymaps (it also had effective_to date) but thought my brain was over complicating it and I was missing an obvious and simple solution.  I will go back and try again from that angle.

[#4]

Attached with answer which might be helpful in your case.

I created an input box called 'Enter Date' . Whatever date you enter there it will filter for  value>= Order Effective date.

If date format is not correct error message will pop up. Also created a button to clear the date value in the input box.

Check it out.

Thanks

AJ

[squeakie_pig]

Hi Ajay

Thank you but it wasn't quite what I was looking for.  I couldn't tell what the 'Enter Date' box was doing that the 'Date1' selection box wasn't?  Both ways work if you want to answer relational database questions "Select all orders with an order date > date selected", but the question I am trying to resolve is "What is the whole record as at the date selected".

1. A user selects a date

2. The data in the table is filtered using max(effective date fieldname) for each table.

I thought the set analysis would need to go something like this:

Count({< date(max(CUST_EFF_DATE)) = {'<=$(=Date(vDate1))'}, date(max(ADD_EFF_DATE)) = {'<=$(=Date(vDate1))'},

date(max(ORDER_EFF_DATE)) = {'<=$(=Date(vDate1))'}>} distinct CUSTOMER_ID)

(Note - am now also looking to see if using FirstSortedValue would be a solution)

[#6]

I am little lost. Do you mind rephrasing the logic of the expression.

I dont quite follow what max is used for.

Lets say the user selects 8/1/2014 then how should the resultant table look like.

[squeakie_pig]

Hi

Apologies, I realised I had made a typo in the original 'End Result?' chart (lack of sleep).  Attached is the latest version and with a few more changes I've been playing with.

Based on the data.  If I select the date 08/01/2014 from the date selector, I would like to see the max(effective date) for the associated field names. 

This way it is recreating the whole record at a given point in time.  The data in this sample is completely fabricated, so questions like "Get a list of all orders with order date > date selected" are not the type of questions I'm trying to answer.  I want to recreate a record at a given time.

I have started playing around with using the FirstSortedValue function and have come up with this:

If a user selects a date (07/01/2014), it will recreate the correct record.

I've only just got to this point though so still trying to get my head around the required > and < syntax I should be using.