Customer experience | Sales, Customer Insights,...

Rollup fields with calculated fields based on date fields

(0) Share

Report

Posted on by EmmaCRMadmin

Hello,

I have created a calculated field which is calculating number of days untill deadline. Next step is that I am trying to create a rollup field for my calculated field on a related entity. But my issue is that I can not find the field it in the list of fields to select from in the dropdown list on my rollup field. I can find other calculated fields i have created before. I am wondering if it has something to do with that the calculated field is based on date fields?

BR,

Emma

I have the same question (0)

All responses (7)

Answers (0)

Wahaj Rashid 11,321 on at

Like (0)

Report
RE: Rollup fields with calculated fields based on date fields

HI,

A rollup can't reference a calculated field that uses another calculated field, even if all the fields of the other calculated field are on the current entity.

You can read about it here:

https://docs.microsoft.com/en-us/previous-versions/dynamicscrm-2016/administering-dynamics-365/dn832162(v=crm.8)?redirectedfrom=MSDN#rollup-field-considerations

Do your calculated field refers to another calculate field?

If yes, you can do following:

Create a whole number field and calculate No of Days using Flow or Workflow.

Then use this simple field in your roll-up query.

Let me know if you need more details.

Best,

Wahaj

(Mark it verified if it answers your question)

Was this reply helpful? Yes No
EmmaCRMadmin on at

Like (0)

Report

RE: Rollup fields with calculated fields based on date fields

Hello Wahaj,

I tried using workflow but I can not add the date fields to my whole number fiels in "set properties" when selecting "update record".

I also tried to change my calculated field to a calculation of 2 simple fields instead, but when I do the following formula it is counting very strange. (DiffInDays(start_date, Now()) / DiffInDays(start_date, deadline)) * 100

For example:

2020-07-20 is the start date

2020-07-22 is today

2020-07-23 is the deadline

the first part of the formula: DiffInDays(start_date, Now()) is calculating 3 days.

and the second part of the formula: DiffInDays(start_date, deadline)) is also calculating 3 days.

To me this makes no sence.

BR,

Emma

Was this reply helpful? Yes No
Suggested answer

Wahaj Rashid 11,321 on at

Like (0)

Report
RE: Rollup fields with calculated fields based on date fields

Hello Emma,

Wrong results are because of time component in the date field. I'm sure you must have set Behavior to "User Local" and Format to Date Only. This way you can use date field in calculation.

However, setting format to Date Only isn't enough, this means time is still stored in the DB, however it doesn't show on the form.

To remove time component, do the following trick

Change date field Behavior from User Local to Time-Zone Independent. This way, DiffInDays will give you correct value. Here is a screen-shot for your reference:

Please note, you can change Behavior from User Local to Time-Zone Independent. However, once changed this cannot be rolled-back (just a side note).

Here is my Number of Days field config:

Here is calculation field formula:

And result:

You can read more about it here:

https://community.dynamics.com/crm/b/dynamicscrmdevdownunder/posts/new-calculated-field-functions-in-crmonline-update-1

Best,

Wahaj

(mark it verified, if it works)

Was this reply helpful? Yes No
Wahaj Rashid 11,321 on at

Like (0)

Report

RE: Rollup fields with calculated fields based on date fields

Hi Emma,

Did you try this?

Thanks,

Wahaj

Was this reply helpful? Yes No
EmmaCRMadmin on at

Like (0)

Report

RE: Rollup fields with calculated fields based on date fields

hello,

Im sorry for my late reply. I tried your suggestion, the calculated field is counting the number of days correctly. But I still can not find the field in the list of options when Im creating my roll-up field...

this is what I wish to do:

Roll-up field: Avg. number of days (account entity)

Calculated field: Number of days (custom entity), and I am using this formula:

(DiffInDays(start_date, Now()) / DiffInDays(start_date, deadline))

the start date field and deadline field is simple date fields.

Best Regards,

Emma

Was this reply helpful? Yes No
Stanley Lai 295 on at

Like (0)

Report

RE: Rollup fields with calculated fields based on date fields

Hi Emma,

I came across this thread as I am experiencing the same issue.

community.dynamics.com/.../rollup-fields-with-calculated-fields-based-on-date-fields

Seems like a known problem

Was this reply helpful? Yes No
Suggested answer

CMilton 110 on at

Like (0)

Report

RE: Rollup fields with calculated fields based on date fields

I believe the issue is with the NOW() function, if I try to use NOW() I get this error when associating with the rollup field:

The target entity filter must use either a simple field or a basic calculated field. It can't use a rollup field, or a calculated field that is using a rollup field

Was this reply helpful? Yes No