calculate difference between date data

[shinymcshires]

I'm trying to figure out if I can use TM1 to create a cube that would store irrigation meter readings. There would only be 2 dims- Customer Number and Meter Reading Date, with the usage/consumption as the data. Is there a way to create another data variable that would calculate the difference between read dates for each account/customer number? I have attached a few screen shots of the TI process:

irrigation meter TI data source.JPG (89.51 KiB) Viewed 9608 times

irrigation meter TI variables.JPG (56.44 KiB) Viewed 9613 times

Once again, I appreciate your feedback and time. Thank you.

[paulsimon]

Hi

There are various ways to do this. The basic function that you need is DAYNO. This gives a serial date number for each date. From the Help DAYNO('98-03-09') returns 13947. You can find this in the Help under Date and Time Rules Functions.

The difference between the DAYNO of the current and previous meter reading will give you the number of days between the readings.

However, the bigger problem is how you identify the previous reading.

One option would be to replace your date dimension with one that just had a list of sequential numbers, eg Slot 1..Slot200.

Then add a measures dimension to hold the meter reading and the date that it was read, which could be stored as a TM1 DAYNO date serial, or as a Text Date. You could also add elements for the days since last reading and units consumed.

You could then have a numeric attribute on the customer dimension to record the number of readings taken for that customer.

As you loaded data, you would retrieve the number of readings taken, and add 1 to this, which would give you the slot in to which you needed to put the data.

You would then load the data in to this slot, and update the number of readings taken. You would then easily be able to reference the previous slot and use CellGetN to retrieve the previous readings. You could then calculate the number of days since the previous reading and the units consumed, and store those in the cube. You could then calculate the units consumed per day.

---

As an alternative you could stay with your date dimension scheme, which you might want in order to allow consolidation, (although you could also use the previous cube to feed a more consolidated view, by taking the movements, eg days between readings and units consumed and consolidating them to a monthly level by customer group).

If you did stay with your date dimension scheme then you will need a way to access the previous reading.

One approach would be to use rules to roll forward the values of date read and meter reading, until interrupted by an entry. You could use a rule along the following lines Result = IF( Input <> 0 then Input else Result from previous day). This sort of scheme is OK in some cases, but the feeding it requires can cause an explosion in memory requirements if used with a detailed dimension containing individual customer.

Instead I would recommend an approach that was similar to the number of readings taken attribute. Assuming that these are domestic customers and you would only ever take at most one reading on any one day, then you could store the date last read as an attribute on the customer dimension. Then when loading data you would get the attribute for the previous date. You could then use that to access the previous meter reading as well. Doing the DAYNO(This Date) - DAYNO(Last Date) would give you the number of days between the readings, and subtracting the meter readings would give you the consumption. I would recommend adding a measures dimension to your cube to allow the days and units to be stored, as well as calculating the average consumption per day.

If you need any further help let me know. I did something similar for electricity consumption a while ago.

Regards


Paul Simon

[ScottW]

I see Paul has just beaten me to it!

My suggestion is basically the same as Paul's first suggestion. Have a uniform "Reading No." dimension with elements "Reading 0001, Reading 0002, ..." The actual reading date would be best stored in a second lookup cube or as a string measure in your main cube (it will still be just as accessible for reporting but calculations in the primary cube will be far simpler.) I would also suggest an additional dimension for measures (reading value, reading date, consumption from last reading, average daily consumption, etc)

If you adopt this structure then differences between reading is easily performed by using consolidations. Time between readings, avg daily consumption, etc cna be easily calculated either at load time or via rules as Paul has already explained using the DAYNO function.

The only other thing that you would need to get this to work would be to store for each customer the last "reading number" or "reading slot". My suggestion would be to store this as an attribute against customer.

HTH

[shinymcshires]

Wow! Great advice and great ideas. I'll probably need to read your posting a few more times to fully understand it. Thank you very much for taking time out to help me. I really appreciate it. I'll read your posting again and give it a shot!

[paulsimon]

Just to clarify although DAYNO is originally a Rules Function, it can also be used in TI

[shinymcshires]

Paul & Scott-

You'll have to forgive me for my relatively limited knowledge of TI. I have built TI processes on my own for a while now, but this is one of the few that has not been straight-forward.

One option would be to replace your date dimension with one that just had a list of sequential numbers, eg Slot 1..Slot200.

- If I understand this correctly, I'll need to create a lookup cube with only one dimension that has a sequence of numbers. I'll use CELLGETN in the TI process of the meter readings cube to load the data into this "slot" dimension i.e. "slot='Slot' | CELLGETN('lookupcubename','slotno'); Is that basically the idea?

Then add a measures dimension to hold the meter reading and the date that it was read, which could be stored as a TM1 DAYNO date serial, or as a Text Date. You could also add elements for the days since last reading and units consumed.

- If I understand this correctly, then I would need to set up measures as a consolidated element (as it would be holding the meter reading elment and the date element?). In order to determine what the "last reading" was, would I use the highest slot number? (i.e. the "last date read" attribute would be determined by the highest slot number?)

You could then have a numeric attribute on the customer dimension to record the number of readings taken for that customer. As you loaded data, you would retrieve the number of readings taken, and add 1 to this, which would give you the slot in to which you needed to put the data.

Would I use another lookup cube with sequential numbers to create the slot numbers?

Thank you both for your feedback and comments!

[Steve Vincent]

I've attached some screenshots that might help explain it for you. (can't put them inline due to changes IT have done to IE over night to prevent this major flaw from hitting us :/). It shows a 3d cube and the elements within each dim. Every client would then have a numeric attribute to determine which "slot" was last (all starting at 0). As you load the data, +1 to that attribute and that will determine which slot to load the value to. You can then load the reading and date to the cube and use a rule to determine the days since last reading and total consumption if needed.

[paulsimon]

I think Steve's post should have answered most of your questions.

With regard to the attribute, right click on your customer dimension and select Edit Attributes. Then insert an Attribute for the Last Reading. Behind the scenes this creates a 2 dimensional }ElementAttributes_Customer cube, which it uses to store the attributes. In TI you can use the ATTRN and ATTRS functions to retrieve the value of an attribute on the customer dimension.

Regards


Paul Simon

[monishapm]

Hi,

I wanted to know whether we can get the difference between two dates in terms of months.( as we can do in oracle and all).
If not please tell me how to do it.


regards,

Monisha