topic Re: Date tables - Sage 200 in Developer

Date tables - Sage 200

Chris2B — Mon, 04 Mar 2024 22:13:59 GMT

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

Re: Date tables - Sage 200

lbendlin — Wed, 06 Mar 2024 17:48:38 GMT

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()))

Re: Date tables - Sage 200

Chris2B — Tue, 05 Mar 2024 10:57:45 GMT

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

Re: Date tables - Sage 200

lbendlin — Tue, 05 Mar 2024 20:36:08 GMT

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

Re: Date tables - Sage 200

Chris2B — Wed, 06 Mar 2024 17:37:19 GMT

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

Re: Date tables - Sage 200

lbendlin — Wed, 06 Mar 2024 17:47:55 GMT

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

Re: Date tables - Sage 200

Chris2B — Wed, 06 Mar 2024 17:54:46 GMT

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

Re: Date tables - Sage 200

lbendlin — Wed, 06 Mar 2024 18:00:42 GMT

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?

Re: Date tables - Sage 200

Chris2B — Wed, 06 Mar 2024 18:47:22 GMT

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

Re: Date tables - Sage 200

lbendlin — Wed, 06 Mar 2024 20:13:16 GMT

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.

Re: Date tables - Sage 200

Chris2B — Wed, 06 Mar 2024 21:37:54 GMT

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?