Date tables - Sage 200

[Chris2B]

Hi I need somehelp with my date table.

I have a live dataset that has been integrated from Sage 200, however I am having a major issue with my date table. It is pulling all the way from 1818 instead of 2018. The dat range that I want it to pull from is 'Invoice Credit Line View'[sop_invoice_credit_document_date]. Its not allowing me to use that as the date table as it says that there are not consecutive dates.

If I can get somehelp I would really appreciate this as I am unable to do any time intelligencemeasures such as SPLY.

KR,

Chris

[Chris2B]

I have tried using Chat GPT to amend the Max values and I have gotten a new error message:

[lbendlin]

that would be in line 18. 

Your overall formula looks like it can be simplified too.  Can you formulate the business rules for earliestdate and latestdate?

[Chris2B]

I've corrected the Max in line 18 and it coes up with this, how I simplyfy it?

[lbendlin]

Why did you change line 26?    needs MAX, not MIN.

At this point it might be easier if you would provide a sample of your data model and data.

[Chris2B]

Sorry it must have been a typo.

I have corrected it and it is still giving me the same message.

thi is the model as it was pulled directly from Sage 200, it is very messy and that is why I am having troubles with it.

I have managed to do basic sales and costs calculations but I am unable to make any time calculated measures.

THis is what the model view looks like, what sample data would you like?

:

[Chris2B]

Sorry I am not understanding? I have tried adding a closing parenthesis but I still am getting errors. I sipossible to help me rewrite it.

Thanks

[lbendlin]

 CALENDAR(MAX(earliestdate,"1900-01-01"),COALESCE(latestdate,TODAY()))

[lbendlin]

Your fact table seems to contain a "May 14 1818" date. So that's what the calendar wants to use.  Problem is that the Windows calendar only starts on December 30 1899. Hence your gap.

Note:  Read about COALESCE , it can simplify your code quite a bit.

Dates = CALENDAR(MAX(earliestdate,"1900-01-01"),COALESCE(latestdate,TODAY()))

[Chris2B]

Hi Ibendin,

Thanks for this, it is a great help. I have tried implementing the COALSCE to remove the gap but I am still getting an error code:

I appreciate the help.

KR,

Chris2b

[lbendlin]

you are missing a closing parenthesis after the second argument of MAX()