[Cloud release: 2025.09.17]
[On-prem release: 2025 October]
[On-prem build: 25.10.10001]
With TARGIT you can create calculations based on time dimensions with dates.
Use case examples:
- In a crosstab containing two date dimensions, e.g., Order Date and Shipment Date, you will be able to calculate the number of days between these two dates.
- In a crosstab containing a single date dimension, you will be able to calculate the number of days between one date and the previous date.
- In a crosstab containing date dimensions, you will be able to calculate the number of days that have passed since a date and until today.
Note: The date calculation functions can only work with single-level dimensions containing dates. However, it is ok to combine date dimensions with other dimensions in the crosstab as in this example:
Prerequisite: The Date Mask
Dates - as any other dimension members - are displayed as strings in the TARGIT client. For date calculations to interpret these strings correctly as dates, we need to supply a Date Mask to the date dimension.
This can be done in two ways:
- Globally, in TARGIT Management Decorations. Setting the date mask on a date dimension in Decorations has the advantage that you only need to set it once to make it work for all Designer users in all objects using this dimension.
- Locally, in the individual documents. With this option, you will need to define the Date Mask every time you work on a new document using date calculations. E.g., if you are working on a cube that you just created from an Excel sheet with Data Discovery, and you want to test some date calculations, then you can immediately set up the Date Mask in the client rather than waiting for it to be available via Decorations.
Generally, when setting up the date mask, it should match the format of the date dimension as you see it in the TARGIT client.
In TARGIT Management Decorations, look up the date dimension and set the 'Date member value mask' property accordingly:
Alternatively, in the TARGIT Designer client, right click the date dimension and select 'Member value' to set the Date Mask:
If the dates are formatted with time stamps, e.g. 08-01-2022 14:20:56, your date mask can be configured like this:
- DD-MM-YYYY ??:??:??
Note: The name of the dimension - as you see it in the client - may be different from the name that you should be using in the calculations. In the Member value settings dialog you can see the "Usage in calculation..." information. The name you see here, is the name you should use in the calculations.
Date Calculations
Once the Date Mask has been defined, you can start working with the date calculations.
| Calculation | Description | Syntax examples |
| rowdatevalue([string],[row ref]) | Use this calculation when dates are presented as rows in the crosstab. Returns an integer value that represents the date. [row ref] is optional. If [row ref] is left out, the default row reference is 0 (current row). | rowdatevalue("Posting Date") rowdatevalue("Posting Date", d-1(s)) rowdatevalue("Posting Date", -7) |
| coldatevalue([string],[col ref]) | Use this calculation when dates are presented as columns in the crosstab. Returns an integer value that represents the date. [col ref] is optional. If [col ref] is left out, the default col reference is 0 (current column). | coldatevalue("Due Date") coldatevalue("Due Date", d1) |
| rowdateisdefined([string],[row ref]) | Use this calculation when dates are presented as rows in the crosstab. Returns true (1) if the referenced cell is inside the scope. [row ref] is optional. If [row ref] is left out, the default row reference is 0 (current row). | rowdateisdefined("Posting Date", -1) |
| coldateisdefined([string],[col ref]) | Use this calculation when dates are presented as columns in the crosstab. Returns true (1) if the referenced cell is inside the scope. [col ref] is optional. If [col ref] is left out, the default col reference is 0 (current column). | coldateisdefined("Due Date", 1) |
| today() | Returns an integer value that represents today's date. If 'Dynamic date origin' has been set to an alternative date, it will return an integer value representing the alternative date. | today() |
Examples
rowdatevalue(“Posting Date”)
rowdatevalue("Due Date") - rowdatevalue("Posting Date")
rowdatevalue("Posting Date") - rowdatevalue("Posting Date",-1)
if rowdateisdefined("Posting Date",-1) then rowdatevalue("Posting Date",-1) else 0
rowdatevalue("Posting Date") - rowdatevalue("Posting Date",d1(s))
today() - coldatevalue("Posting Date")
Comments
This Feature is really great !!!
I have so many usecases where I had to calculate all these values in the Datawarehouse. I just checked, modified the Date Mask, combined it with the today Function and... It works perfect!
Thanks for this great Feature!
Absolutely love this feature. I'm beyond impressed – this is a game-changer!
Really nice feature.
Working with dates/dynamic time in Targit was already good, but now it just got even better.
Please sign in to leave a comment.