Category: Business objects days between two dates

Business objects days between two dates

I came across a thread some days back where an end user wanted the Age of a customer in X Years Y Months and Z Days format given tow dates. I tweaked the logic which I found in one of the blog and got that working so thought of sharing with all of you as we may come across such requirements in future. So here how it goes.

First I will calculate the number of years in a formula which I have been told by an expert that breaking a huge formula into smaller pieces makes it more readable and easy to understand. I have tested the code with Leap year Dates and some other random dates but your valuable feed backs and suggestions are always welcome. I need to calculate and display as an age, in Years Months and Days the age of a person on the date at which an event occurred.

Your help would be much appreciated. Former Member. Posted on April 22, 1 minute read. Follow RSS feed Like. Hope you like it. Regards Niraj. Alert Moderator. Assigned tags. Related Blog Posts. Related Questions. You must be Logged on to comment or reply to a post. Mahboob Mohammed. April 28, at pm. Great effort Niraj. Thanks, Mahboob Mohammed. Like 0. Former Member Post author. Good catch Mahboob!!

Thanks again for your feedback like you always do. May 25, at am. May 25, at pm. Thanks Akshat for your inputs I will test it out and get back to you. May 13, at pm. Link Text. Open link in a new tab.

No search term specified. Showing recent items. Search or use up and down arrow keys to select an item.Hello guys, I have 2 columns named, start date and end date, I want to create a column which contains all the dates between the after mentioned dates, for example.

From [StartDate]. Just expand that list-column and format to date. ImkeF it works, but when I expand the lists, the primary key in my table gets duplicated, so Power BI gives me an error when applying the query, I tried creating a new table, adding a new custom column with the formula you mentoined in addition of the table name besides the column name, but it gives me this error in every row of the created column.

Not sure if I could follow you here, but the error-message suggests that you are trying to convert a list instead of a number. So is it possible that you haven't expanded the list yet? I reproduced you error. Can I use M in the table itself or does it have to be in queryeditor? I ask because my table is already changed.

I have a similar situation, where I wanted to get the list of dates between 2 dates. It worked perfectly. Except the the values corresponding to those list of dates are summed together. Error: We cannot apply field access to the type Function. Turn on suggestions. Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type. Showing results for. Search instead for.

business objects days between two dates

Did you mean:. All forum topics Previous Topic Next Topic. Getting all dates between 2 dates. Message 1 of Re: Getting all dates between 2 dates.

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark my post as a solution. Message 2 of ImkeF it works, but when I expand the lists, the primary key in my table gets duplicated, so Power BI gives me an error when applying the query, I tried creating a new table, adding a new custom column with the formula you mentoined in addition of the table name besides the column name, but it gives me this error in every row of the created column "DataFormat.

Error: We couldn't convert to Number. Details: List". Message 3 of Otherwise please share query-code or file. Message 4 of AlexChen Microsoft. Please change them to Date type then follow the steps below.

Message 5 of Message 6 of I have a date object, Sales Date, that I want to display as a string in the format year month — 25th Jan should show as The key thing to note here is the capitalisation of months — MM is used for months and mm is used for minutes.

This applies to both FormatDate and ToDate. ToDate allows you to pass a character string or object and converts this to a date, based in the input mask provided.

Subscribe to RSS

I have been given a flat file input and want to convert the date string object that represents a date to a date. An example date string is — 26th June Note that the input format reflects the format of the character string, not the date format that you want the date to be displayed in when using it.

Input format is about the way that the date string is structured; what you should do is format your new date object using the right-click, format number functionality. So what does that do? Well, working from the inside, the first thing done is to get the sales year month — in our example above — as a string. Then, we tag 01 on the end of it, to make it a date string We then apply ToDate to that, specifying the correct input mask.

The key take away from this is to remember that for ToDate, the format that you are specifying relates to the object you are placing into the function, not the desired output date.

SAP BusinessObjects Reporting Cookbook by Yoav Yahav

This is the opposite for FormatDate, where you are specifying the output format. Another use for this functionality has come about with the latest Webi release, 4. This will make things like comparing budgets to sales easier; budgets are typically for the month and sales by day.

Now you can format your sales date as a month, you can merge this new variable with your budget month and compare the two easily.

I saw your comments on one of the posts on the Webi forum where in you said its possible to navigate between tabs using input control from Webi 4. Can you please tell me the steps involved to do that? I created a post for this topic and it will be great if you can answer there. Former Member. Posted on July 28, 2 minute read. FormatDate and ToDate — a simple guide. Follow RSS feed Like. FormatDate What does it do?

FormatDate allows you to pass in a date object and outputs a string in the format specified. ToDate What does it do? Alert Moderator. Assigned tags. Related Blog Posts. Related Questions. You must be Logged on to comment or reply to a post. December 15, at pm. Hi Mark, I saw your comments on one of the posts on the Webi forum where in you said its possible to navigate between tabs using input control from Webi 4.

Like 0.

business objects days between two dates

Link Text.Which works fine, but obviously this will count the weekends, has anybody got an idea on how i would approach this? Go to Solution.

Then create relationship between holiday table and your 'Repot' table. Create relationship between Holiday table and Date Table. Use if function to decide if the day is work day. Finally, create a measure to calculate the number od holidays. And create a card visual to display the result. If you have other issues, please let me know.

View solution in original post. Thanks for this, however, this is an akward workaround, it relies on the table being updated every year and it being populated accuratly for a whole year to start with. While we could make a central "Holidays" table for all users to link too, its the kind of thing that will always get left behind due to its low priority vs other tasks.

I guess, coming from Tableau, where you just clicked on "Weekdays" and it worked it all out for you, I was looking for something much the same. I guess I was looking for a more automated solution, I found it odd that PowerBi does not have a calendar function built in. Given that Outlook is an MS updated calendar at regional levels. I suppose coming from Tableau and others like it, I expected it to be an included functionality. I understand holidays might be harder to work out, but I would have thought weekdays to be more straigt forward.

There is Calendar function in PowerBI, you can click New Table under Modeling on home page, create a date table using the calendar function. Best Regards, Angelia. Thanks for that, I did not know that yet, only been working with PBi for 2 weeks, so still finding my feet. My team build reports and dashboards for the rest of the company.

I worry that if these do not auto populate each year, then I will be seeing a large amount of requests to fix date issues. I will have to have a think about how to approach this long term, but for now this should suit my needs.

Please mark the reply as answer if it is helpful for you, which will help more people find workaround. Thanks for understanding. Turn on suggestions. Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type. Showing results for. Search instead for. Did you mean:. All forum topics Previous Topic Next Topic. Many Thanks. Ben Solved! Labels: Need Help. Everyone's tags 1 : datediff. Message 1 of Accepted Solutions. Best Regards, Angelia View solution in original post.

Message 4 of I'm using Business Objects 6. Client 1 value 1 value 2 value n Clinet 2 value 1 value 2 value n Please suggest a solution. Unfortunately, the question is not in calculating difference between two dates, but the difference between two values I have for this dates in a crosstable. For example, in a source table I've got Dates, Clients and a Balance sum for each client in each date.

I mean I have the source data like this: Date Client Balance date 1 client 1 value 1 date 1 client 2 value 2 date 2 client 1 value 3 date 2 client 2 value 4 date n client 1 value 5 date n client 2 value 6. It appears you're calculating within the default context, so specifying the level wouldn't help. Have you tried creating a separate variable for the min and max dates and substituting that in your formula? So after all my attempts I suppose that "Where" can be used only with an exact value.

Maybe, is there a way to make a calculation like this not with "Where", but using stmth else? Can you use If Then instead of Where? Big Data. Tech Sign In Page. Forgot Password? Don't have an account? Sign up. Follow Tech Sign In Page. March 06, AM. Something went wrong on our end. Please try again later. March 08, PM. Is there any way to make calculations like this on the report level? March 14, AM. I have experienced issues in BO where defining functions at an individual level helped.

Try this if you haven't figure out your solution yet: 1. March 16, AM. March 17, AM.

Excel Workday function: Find end date - exclude weekends & holidays

Unfortunatly, it doesn't work. Sign In to Post a Comment. Sign In. Hide Comments View Comments. Looking for more? Search the archive of solutions and how-to's. Ask a new question to a community of peers.Need support for your remote team?

Check out our new promo! IT issues often require a personalized solution. Why EE? Get Access. Log In. Web Dev. NET App Servers. We help IT Professionals succeed at work. Business Objects report add 15 days to a date. Pdeters asked.

business objects days between two dates

Medium Priority. Last Modified: I have a date field in a report in Business Objects. I want to create another field in the report that adds 15 days to that date. Start Free Trial. View Solution Only. Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.

Most Valuable Expert This award recognizes tech experts who passionately share their knowledge with the community and go the extra mile with helpful contributions. Top Expert This award recognizes someone who has achieved high tech and professional accomplishments as an expert in a specific topic.

Commented: Author Commented: Walter Ritzel Senior Software Engineer. Humm, are you using Infoview to create reports? Or are you Crystal Reports? Not the solution you were looking for? Getting a personalized solution is easy. Ask the Experts. Explore More Content.Oct 23 The easiest answer is to use a calendar table with a flag for business days because that takes care of everything quite easily.

You can even mark holidays along with weekend days and make the results more useful. Can you do that?

Displaying Age of person in X Years Y months and Z days from two dates..

The answer is coming if you read the rest of the post. I had written this blog post some time ago but waited to release it until after the presentation had been delivered. The presentation has been uploaded to my blog and is available for download from the conference page. This formula is going to use the following functions, so I will introduce and describe each of them a bit here. One of the first steps in solving any sort of calculation is to look for shortcuts.

Clearly there will be an infinite number of input values for my calculation so I need some way to avoid too many possible choices. Because of this I am going to start by counting the number of weeks between two days.

Take a look at the calendar on the right-side of my blog and try an experiment. It turns out that no matter which day of the week you start on, if you run for seven days you are guaranteed to get exactly two weekend dates. No more, no less, exactly two. Try it out and see. Start on a Sunday and count forward seven days and what do you get? Start on Monday and count forward seven days and the same thing happens. Start on Tuesday… on Friday, or even Saturday… and any range of seven days always has exactly two weekend dates included.

I can extend this to any multiple of seven days. If I have 14 days I get 4 weekend days. With 21 days I have 6 weekend days. If I multiply by five instead of two I get the number of weekdays in the range, therefore I am going to start my calculation using this pattern.

Here is the start of the formula:. Let me break that down. First the DaysBetween function is used to determine the number of days between the starting and ending dates for my range.


COMMENTS

comments user
Daigis

Welche anmutige Antwort