Solved

Using date functions with a Day dimension as an argument to the function

  • 30 August 2023
  • 2 replies
  • 63 views
K
Rank
Badge

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

 

 

icon

Best answer by francois 30 August 2023, 22:28

View original

2 replies

francois
Rank
Userlevel 6
Badge +7

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

François (Pigment)
K
Rank
Badge

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.

TIMEDIM('EOP Subscribers'.'EOP Date'.'Start Date'+1,Day)

It looks something like this:

Pretty basic stuff once I used your tip on using the property.  Thanks for the assist on my specific question.

Reply


Ask your question here!

Terms & ConditionsCookie settings