{"id":2618,"date":"2017-06-06T16:33:43","date_gmt":"2017-06-06T22:33:43","guid":{"rendered":"http:\/\/www.designandexecute.com\/designs\/?p=2618"},"modified":"2021-03-03T12:32:35","modified_gmt":"2021-03-03T18:32:35","slug":"how-to-calculate-number-of-days-in-a-month-or-month-to-date-mtd-in-webintelligence-webi","status":"publish","type":"post","link":"https:\/\/www.designandexecute.com\/designs\/how-to-calculate-number-of-days-in-a-month-or-month-to-date-mtd-in-webintelligence-webi\/","title":{"rendered":"How to Calculate Number Of Days in a Month or Month To Date (MTD) in WebIntelligence (webi)"},"content":{"rendered":"

We will need to know a couple of useful Webi and Rich Client functions: namely LastDayOfMonth, DayNumberOfMonth , CurrentDate(), ToNumber(), Left()<\/strong><\/p>\n

How to get Number of days in the current Month<\/h2>\n

For a specified date, the LastDayOfMonth<\/strong> will give you the last day in that month.<\/p>\n

so =LastDayOfMonth(CurrentDate())<\/strong> will give you the last day of the current month then to use this in calculations you must convert it to a number using the DayNumberOfMonth<\/strong> function<\/p>\n

=DayNumberOfMonth(LastDayOfMonth(CurrentDate()))<\/strong><\/p>\n

How to get the day of the current day or the MTD value<\/h2>\n

We need to do three things<\/p>\n

    \n
  1. Get the current date in a format we can guarantee getting the days part of the date<\/li>\n
  2. Format it so we can get the days of the date by formatting it with the days first<\/li>\n
  3. convert it to numbers to use it in calculations<\/li>\n<\/ol>\n

    Step1<\/p>\n

    =FormatDate(CurrentDate();\u201ddd\/MM\/yyyy\u201d)<\/strong><\/p>\n

    Step2<\/p>\n

    =Left(FormatDate(CurrentDate();\u201ddd\/MM\/yyyy\u201d);2)<\/strong><\/p>\n

    Step3<\/p>\n

    =ToNumber(Left(FormatDate(CurrentDate();\u201ddd\/MM\/yyyy\u201d);2))<\/strong><\/p>\n

    How to a create a partial prorated MTD value<\/h2>\n

    this is done in 2 steps<\/p>\n

    step1: to get the value for 1 day<\/p>\n

    =\u00a0 (VALUE\/ Days in Current month)<\/p>\n

    step2: get the amt for the days so far in the month<\/p>\n

    =\u00a0 (VALUE\/ Days in Current Month) * Current day<\/p>\n

    use your VALUE and first part and second part of this article<\/p>\n

    = (Monthly VALUE \/ DayNumberOfMonth(LastDayOfMonth(CurrentDate())) <\/strong>) \/ ToNumber(Left(FormatDate(CurrentDate();\u201ddd\/MM\/yyyy\u201d);2))<\/strong><\/p>\n

    Sharing is Caring. Thanks for reading and Sharing \u2764<\/span><\/strong><\/p>\n

    Like or Follow me On: <\/span><\/strong>Instagram<\/span><\/strong><\/a>\u00a0|\u00a0<\/span><\/strong>Facebook<\/span><\/strong><\/a>\u00a0|\u00a0<\/span><\/strong>Linked in<\/span><\/strong><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

    We will need to know a couple of useful Webi and Rich Client functions: namely LastDayOfMonth, DayNumberOfMonth , CurrentDate(), ToNumber(), Left() How to get Number of days in the current Month For a specified date, the LastDayOfMonth will give you the last day in that month. so =LastDayOfMonth(CurrentDate()) will give you the last day of […]<\/p>\n","protected":false},"author":2,"featured_media":2667,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,30],"tags":[37,36,34,35],"class_list":["post-2618","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-bi-dashboards-analytics","category-bi-business-objects","tag-days-in-month","tag-mtd","tag-webi","tag-webintelligence"],"jetpack_featured_media_url":"https:\/\/www.designandexecute.com\/designs\/wp-content\/uploads\/2017\/06\/calendar-icon.jpg.png","_links":{"self":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/2618","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/comments?post=2618"}],"version-history":[{"count":5,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/2618\/revisions"}],"predecessor-version":[{"id":11239,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/posts\/2618\/revisions\/11239"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/media\/2667"}],"wp:attachment":[{"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/media?parent=2618"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/categories?post=2618"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.designandexecute.com\/designs\/wp-json\/wp\/v2\/tags?post=2618"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}