Retrieve data from an entity within a year on a quarterly basis. (3 months to 3 months)
Hello, I am developing an application which sells Products. And when invoicing there is a place in the form to add Tax amount. The Tax amount gets saved in an entity named "TAXES". Now my client needs a way to calculate the total taxes within one year but in a quarterly basis. Meaning, from January to March, April to June, July to September and so on. I have tried it with a Retrieve Microflow on the Entity "Taxes" with Xpath restriction on the date value. Here is where the trouble comes: When Retrieving I need to do this automatically without user intervention via a form for setting the specific date range. Means I need to hard code it in the retrieve action. But How to do this? I know if we use a form to capture the user inputs for a date range we can easily apply that to the microflow retrieve. But in my case I need to do this for each year and per three months automatically. I assume that I can hard code the months, but not the year because this has to be done automatically each year. Not just one year. If I hard code the whole date with the year, then it will be useless. So the current Year has to be loaded Automatically and the dates can be fixed. What is the Xpath code for getting the current year and fixed date range for a retrieval like this? Please help Dylan [EDIT] I am editing my question and answering my solution based on every one's ideas here, because can't type the whole thing in comments. I do applogize for that. And I thank every one for their ideas. So this is my result: ** Created Date Time Variables with the following. $Start_Of_Year = [%BeginOfCurrentYear%] $End_Of_March = addMinutes(addDays(addMonths($Start_of_Year,3),-1),-1) $Start_of_April = addMinutes(addMonths($Start_of_Year,3),-1) $End_Of_June = addMinutes(addDays(addMonths($Start_of_Year,6),-1),-1) $Start_Of_July = addMinutes(addMonths($Start_of_Year,6),-1) $End_Of_September = addMinutes(addDays(addMonths($Start_of_Year,9),-1),-1) $Start_Of_October = addMinutes(addMonths($Start_of_Year,9),-1) $End_Of_December = addMinutes(addDays(addMonths($Start_of_Year,12),-1),-1) ** Xpath Retrieval Code: * 1st Quater; [Date>$Start_of_Year and Date<$End_Of_March] *2nd Quater [Date>$Start_Of_April and Date<$End_Of_June] And so on... ** The result I got in dates: * 1/1/2017 to 3/31/2017 * 4/1/2017 to 6/30/2017 * 7/1/2017 to 9/30/2017 * 10/1/2017 to 12/31/2017 Thanks again, hope this was a productive question for every one. Dylan
In your microflow, you can begin with a datetime variable that is set to the token [%BeginOfCurrentYear%] - lets call it $StartOfYear, and one called $EndOfYear set to the token [%EndOfCurrentYear%]
From there, you can do date arithmetic for quarters, for instance create two more date variables called $StartOfQuarter and $EndOfQuarter. Set $StartOfQuarter to [%BeginOfCurrentYear%] set $EndOfQuarter to addMinutes(addMonths($StartOfQuarter,3),-1), which will give you the end of the quarter, etc.
You can then retrieve from the database using those variables and store the results in an entity that you use to create the reports you need.
Hope that helps,
I would like to comment on Mike's answer because I do not like the addMinutes(addMonths($StartOfQuarter,3),-1) part because you would miss the sales in the last minute of that quarter. Just create a date with the first of april and use the smaller then option in a XPath to retrieve the right objects and avoid to do the substraction trick because you might run into all kind of problems. Some databases for instance use as end of day .998 instead of .999 milliseconds etc.
As said by Mike create in a microflow variables, like January and fill the date with the Xpath keyword [%BeginOfCurrentYear%]. See the documentation here: https://docs.mendix.com/refguide/xpath-keywords-and-system-variables Note though that the documentation is incomplete here because the year keywords are missing here. Now create April and we gone fill that one with the command addMonthsUTC($January,3). You can then use these variables in an XPath [SomeDate >= $January and Somedate <$April].
Mike and Rendal give great answers, I wanted to also share the way I ussully do this. I try to avoid using addMonths and addYear to get a certain date, instead I find it easier to parse a date e.g. to get the first quarter i would use something line
$startDate = parseDateTimeUTC('01.01.'+$Year,'dd.MM.yyyy')
$endDate = parseDateTimeUTC('01.04.'+$Year,'dd.MM.yyyy')
//Module.Entity[Date >= $startDate and Date < $endDate]
Just to be clear I am not saying that this approach has some benefits over the other answers. If anyone can comment on any drawbacks of this approach, I give a penny for their thoughts.