Skip to main content
Solved

Merging Text & Date Dimensions

  • December 15, 2025
  • 2 replies
  • 85 views
M
Apprentice Author

Hi Everyone,
I would like to merge two Dimensions into a new Text Dimension in order to get a Unique ID, using a Formula
But the trick is that one of the two Dimension is a Date Data Type :

Dim1 : Item.Code (Text Type)
Dim2 : StartDate (Date Type)

Formula result : Item.code &”_”& StartDate

How to make this formula ?

Thanks in advance
Matthieu

Best answer by Laszlo

Hi ​@Matthieu Wallet 

 

You can accomplish this by adding ID properties into your Dim1, Dim2 and Dim3 lists (Dim3 being the resulting dimension list), and then using those IDs to to map the generated combinations into the Dim3 list.

  1. First, generate the ID numbers by using these formulas:
  • CUMULATE(1 , Dim1)
    CUMULATE(1 , Dim2)
    CUMULATE(1 , Dim3)

 

  1. Then, prepare the Dim3 placeholder items. You’ll need add at least as many items in Dim3 as the total of the generated combination of Dim1 and Dim2, which is Dim1 items * Dim2 items. These will serve as the placeholders for the generated values.
  1.  Then in the relevant property of Dim3 (in my example it’s the Name), add a formula to generate the Dim1 and Dim2 combinations in the required format.
    The Dim1.ID and Dim2.ID are used to calculate the corresponding Dim3.ID and assign the results onto those IDs in the Dim3 list. The final formula looks like this:
( Dim1.Code & "_" & YEAR(Dim2.StartDate) & "/" & MONTH(Dim2.StartDate) & "/" & DAY(Dim2.StartDate) ) 

[BY FIRST: 
ITEM(
  IFDEFINED(
    ( Dim1.Code & "_" & YEAR(Dim2.StartDate) & "/" & MONTH(Dim2.StartDate) & "/" & DAY(Dim2.StartDate) ) 
    , (Dim1.ID-1) * Dim2.ID )[REMOVE LASTNONBLANK: Dim2]
   + Dim2.ID
,Dim3.ID
)]

 

  • Dim1.Code & "_" & YEAR(Dim2.StartDate) & "/" & MONTH(Dim2.StartDate) & "/" & DAY(Dim2.StartDate)

    This part of the formula generates the Dim3 values in the format of D1_2025/12/12 . You can adjust this part as needed to match your formatting requirements.

  • The [BY FIRST ...] part calculates the Dim3 IDs of the generated combinations, and assigns the results to the Dim3 list.

 

This image visualises the dimension lists.

 

Cheers,

Laszlo

2 replies

Laszlo
Master Helper
Forum|alt.badge.img+8
  • LaszloMaster Helper
  • Master Helper
  • Answer
  • December 15, 2025

Hi ​@Matthieu Wallet 

 

You can accomplish this by adding ID properties into your Dim1, Dim2 and Dim3 lists (Dim3 being the resulting dimension list), and then using those IDs to to map the generated combinations into the Dim3 list.

  1. First, generate the ID numbers by using these formulas:
  • CUMULATE(1 , Dim1)
    CUMULATE(1 , Dim2)
    CUMULATE(1 , Dim3)

 

  1. Then, prepare the Dim3 placeholder items. You’ll need add at least as many items in Dim3 as the total of the generated combination of Dim1 and Dim2, which is Dim1 items * Dim2 items. These will serve as the placeholders for the generated values.
  1.  Then in the relevant property of Dim3 (in my example it’s the Name), add a formula to generate the Dim1 and Dim2 combinations in the required format.
    The Dim1.ID and Dim2.ID are used to calculate the corresponding Dim3.ID and assign the results onto those IDs in the Dim3 list. The final formula looks like this:
( Dim1.Code & "_" & YEAR(Dim2.StartDate) & "/" & MONTH(Dim2.StartDate) & "/" & DAY(Dim2.StartDate) ) 

[BY FIRST: 
ITEM(
  IFDEFINED(
    ( Dim1.Code & "_" & YEAR(Dim2.StartDate) & "/" & MONTH(Dim2.StartDate) & "/" & DAY(Dim2.StartDate) ) 
    , (Dim1.ID-1) * Dim2.ID )[REMOVE LASTNONBLANK: Dim2]
   + Dim2.ID
,Dim3.ID
)]

 

  • Dim1.Code & "_" & YEAR(Dim2.StartDate) & "/" & MONTH(Dim2.StartDate) & "/" & DAY(Dim2.StartDate)

    This part of the formula generates the Dim3 values in the format of D1_2025/12/12 . You can adjust this part as needed to match your formatting requirements.

  • The [BY FIRST ...] part calculates the Dim3 IDs of the generated combinations, and assigns the results to the Dim3 list.

 

This image visualises the dimension lists.

 

Cheers,

Laszlo

Benoit
Community Manager
Forum|alt.badge.img+15
  • BenoitCommunity Manager
  • Community Manager
  • December 15, 2025

Hi,

In addition to Laszlo response.

To convert a date into a text, a solution would be to use the TIMEDIM function to transform the date into a time dimension and then retrieve the text property that better fit your need.

You just need to have the Day dimension activated in your calendar settings.

 

The formula would look like this:

TIMEDIM(Dim2.StartDate,Day).’dd-MM-YYYY’

 

But you can choose any text property within your Day dimension:


 

You can even create a new custom text property in your Day dimension if you need.

 

Hope this helps.

Benoit - Pigment wizard

Ask your question here!

Terms & ConditionsCookie settingsAccessibility statement