How to get calculated metric with date range

[ShivaniM]

Hello Experts,

Can I add a datetime field in CJA dataview which has 365 days plus existing field.

Example:  Field A is exisitng which has date values.

I need Field B = Field A (13th March 2024) + 365 days = 13th March 2025

[Jennifer_Dungan]

Hi, 

I don't have CJA myself, but I do have access to a CJA sandbox... I was taking a look in the calculated metric builder, but it seems that the outputs are limited to decimal, time, percent or currency...

I am also sure that this would still have to use metrics, and not dimensions, and I don't think that creating a derived field for "make a metric from dimension" will yield the results you are looking for... I believe that it will just make a simple counter based on when the dimension has a values.

Will the math always hold true at +1 year? Maybe you will just have to create a new dimension and calculate the +365 days. Also what about leap years? You might need some more complexities to this.

The other option is to use Report Builder, and to pull the data into Excel and use Excel's formulas to calculate out the "date + some amount of time".

[ShivaniM]

Hi Jennefer, 

Thanks for the response. For derived field also with date function I'm facing restrictions to use the addition.

I want to create a churn date, as if user didn't purchase anything for 1 year then it should be considered as churned customer. So on average, I'm taking 365 days.