Using date functions with a Day dimension as an argument to the function
I have a transaction list with a date as a dimension. This is done so that I can leverage the “delete existing items / limited scope” import functionality as the source data is purged after a 3 year rolling window. Because the date has to be a dimension to do this, I am having difficulty creating the most basic functions on handling the date in my import. It seems that every date function gives me an error that an argument must be of type date. Is there a function I can use to convert the dimension into a date type (i.e., a nested function) so I can use the date functions? I realize I could just change the field in the transaction list to date but I really want to leverage the limited scope functionality. Any insights would be much appreciated.
Kevin
Page 1 / 1
Hi Kevin,
It’s definitely a tricky one because you’re mixing a lot of things here. I’ve made it work in the past, so this is definitely doable. Starting with your destination, you should try to end up with a date in your transaction. To do this, let’s explore two options:
Using DATE or DATEVALUE
The functions DATE and DATEVALUE allow you to turn respectively numbers and strings into a date.
Looking at the examples of DATEVALUE, I think we could just use your default name
So in your transaction, you could simply use DATEVALUE(Transaction.’EOP Date’.Name, “yyyy-MM-dd”)
I am adding .Name here because we want to work with the text value, not the item of your dimension EOP Date.
The second option is about importing directly the date as a date-formatted property in your EOP Date dimension.
Currently, you’re importing new items by mapping them to a unique Name property that is text-formatted. You could add another Date date-formatted property, and import your transaction’s EOP Date property using that property, by clicking on the cog in the import configuration and choosing the date instead of the name.
This might look like a more abstract solution though, so it’s up to you. The first option maps the text to a date using a formula, the second one using the import mapping functions. The best one to use would be the one you’re most comfortable with.
Hope this helps! François
Huge help and got me thinking about how to use the dimension properties. So, to use the date, I needed to add a date formatted property from the dimension. For this particular case, I just added 1 day to the date and used the TIMEDIM function.
