logo
down
shadow

Assigning Dates to Fiscal Year


Assigning Dates to Fiscal Year

By : Chieu_Xuan
Date : November 22 2020, 02:59 PM
I hope this helps you . Here are some alternatives. They all return numeric years but if you really need a string starting with FY then use paste0("FY", result) where result is any of the results below. They all support vector input, i.e. the input dates can be a vector.
1) zoo::as.yearmon The zoo package has a "yearmon" class which represents year/months as year + fraction where fraction = 0 for jan, 1/12 for feb, 2/12 for march and so on.
code :
library(zoo)

as.integer(as.yearmon(dates) - 4/12 + 1)
## [1] 2016 2015 2015
lt <- as.POSIXlt(dates)
lt$year + (lt$mo >= 4) + 1900
## [1] 2016 2015 2015
as.numeric(format(dates, "%Y")) + (format(dates, "%m") >= "05")
## [1] 2016 2015 2015
as.numeric(substr(dates, 1, 4)) + (substr(dates, 6, 7) >= "05")
## [1] 2016 2015 2015
with(read.table(text = format(dates), sep = "-"), V1 + (V2 >= 5))
## [1] 2016 2015 2015
dates <- as.Date(c("2015-05-01", "2015-04-30", "2014-09-01"))


Share : facebook icon twitter icon
How to make a dynamic report on weekly totals of fiscal year where fiscal year can change

How to make a dynamic report on weekly totals of fiscal year where fiscal year can change


By : Roman Postanciuc
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further Have a look at examples for Calendaring Syntax of DBMS_SCHEDULER. By default this is used for SCHEDULER JOBS, however I don't see any reason not using it for other purpose.
You can do it like this:
code :
BEGIN
    DBMS_SCHEDULER.CREATE_SCHEDULE('fiscal_year', repeat_interval => 'FREQ=YEARLY;BYDATE=0701');
END;


CREATE OR REPLACE FUNCTION GetFiscalYear(theYear IN TIMESTAMP DEFAULT LOCALTIMESTAMP) RETURN TIMESTAMP IS
    res TIMESTAMP;
BEGIN
     DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=fiscal_year;BYDAY=-1 SUN', NULL, ADD_MONTHS(TRUNC(theYear, 'YYYY'), -12), res);
    RETURN res;
END;
SELECT 
    TO_CHAR(GetFiscalYear(ADD_MONTHS(LOCALTIMESTAMP, -12)), 'yyyy-mm-dd fmDay') AS last_year,
    TO_CHAR(GetFiscalYear(), 'yyyy-mm-dd fmDay') AS this_year,
    TO_CHAR(GetFiscalYear(ADD_MONTHS(LOCALTIMESTAMP, 12)), 'yyyy-mm-dd fmDay') AS next_year
FROM dual;

LAST_YEAR            THIS_YEAR           NEXT_YEAR    
-------------------  ------------------  ----------------------
2014-06-29 Sunday    2015-06-28 Sunday   2016-06-26 Sunday

1 row selected.
CREATE OR REPLACE FUNCTION GetFiscalYear(theYear IN TIMESTAMP DEFAULT LOCALTIMESTAMP) RETURN TIMESTAMP IS
    res TIMESTAMP;
BEGIN
    -- Last Sunday of June 
    DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=YEARLY;BYMONTH=JUN;BYDAY=-1 SUN', NULL, ADD_MONTHS(TRUNC(theYear, 'YYYY'), -12), res);
    RETURN res;
END;
Check two dates are in same fiscal year in SQL condition?

Check two dates are in same fiscal year in SQL condition?


By : Mehmet T.
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , For the first part of question - why is your condition not working as expected. In the first part where both dates are in the same year you are comparing only March and April. And the second part is just mixing previous and next year wrongly - you have to get rid of ABS and split in two OR conditions
Fix:
code :
NEW.data<>OLD.data
AND 
(
   (
      YEAR(NEW.starting_date) = YEAR(OLD.starting_date) 
      AND
      ( 
         (MONTH(NEW.starting_date)<=3 AND MONTH(OLD.starting_date)<=3)
         OR
         (MONTH(NEW.starting_date)>=4 AND MONTH(OLD.starting_date)>=4)
      )
   )
   OR 
   (
      YEAR(NEW.starting_date)-YEAR(OLD.starting_date) = 1
      AND 
      MONTH(NEW.starting_date)<=3 AND MONTH(OLD.starting_date) >=4
   )
   OR
   (
      YEAR(NEW.starting_date)-YEAR(OLD.starting_date) = -1
      AND 
      MONTH(NEW.starting_date)>=4 AND MONTH(OLD.starting_date) <=3
   )
)
NEW.data<>OLD.data
AND CASE WHEN MONTH(NEW.starting_date) >= 4 
           THEN YEAR(NEW.starting_date) ELSE YEAR(NEW.starting_date) - 1
    END 
= CASE WHEN MONTH(OLD.starting_date) >= 4 
           THEN YEAR(OLD.starting_date) ELSE YEAR(OLD.starting_date) - 1
    END 
generate seasonal plot, but with fiscal year start/end dates

generate seasonal plot, but with fiscal year start/end dates


By : Sawa Giovanni
Date : March 29 2020, 07:55 AM
Hope that helps Hello! Is there a way to index a chart to start and end at specific points (which may be out of numeric order)? , You just need to set the desired levels for the Month inside factor
code :
library(magrittr)
library(tidyverse)
library(lubridate)
library(plotly)

Date <- seq(as.Date("2016-10-1"), as.Date("2018-09-01"), by = "month")
values <- c(2, 3, 4, 3, 4, 5, 6, 4, 5, 6, 7, 8, 9, 10, 8, 9, 10, 11, 12, 13, 11, 12, 13, 14)
YearEnd <- c(
  2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017, 2017,
  2018, 2018, 2018, 2018, 2018, 2018, 2018, 2018, 2018, 2018, 2018, 2018
)
df <- data.frame(Date, values, YearEnd)


# to fiscal year order
df %<>%
  mutate(
    Month = month(Date),
    YearEnd = factor(YearEnd)) %>%
  mutate(Month = factor(Month,
    levels = c(10:12, 1:9),
    labels = c(month.abb[10:12], month.abb[1:9])))
df

#>          Date values YearEnd Month
#> 1  2016-10-01      2    2017   Oct
#> 2  2016-11-01      3    2017   Nov
#> 3  2016-12-01      4    2017   Dec
#> 4  2017-01-01      3    2017   Jan
#> 5  2017-02-01      4    2017   Feb
#> 6  2017-03-01      5    2017   Mar
#> 7  2017-04-01      6    2017   Apr
#> 8  2017-05-01      4    2017   May
#> 9  2017-06-01      5    2017   Jun
#> 10 2017-07-01      6    2017   Jul
#> 11 2017-08-01      7    2017   Aug
#> 12 2017-09-01      8    2017   Sep
...

p1 <- ggplot(df, aes(
  x = Month, y = values,
  color = YearEnd,
  group = YearEnd)) +
  geom_line() +
  theme_classic(base_size = 12)

ggplotly(p1)
# Generate random data
set.seed(2018)

date = seq(from = as.Date("2016-10-01"), to = as.Date("2018-09-30"),
           by = "days")
values = c(rnorm(length(date)/2, 8, 1.5), rnorm(length(date)/2, 16, 2))
dat <- data.frame(date, values)

df <- dat %>%
  tbl_df() %>%
  mutate(jday    = factor(yday(date)),
         Month   = month(date),
         Year    = year(date),
         # only create label for the 1st day of the month
         myLabel = case_when(day(date) == 1L ~ format(date, "%b-%d"),
                             TRUE ~ NA_character_)) %>% 
  # create fiscal year column
  mutate(fcyear = case_when(Month > 9 ~ as.factor(Year + 1),
                            TRUE      ~ as.factor(Year))) %>% 
  mutate(Month = factor(Month,
                        levels = c(10:12, 1:9),
                        labels = c(month.abb[10:12], month.abb[1:9])))
df

#> # A tibble: 730 x 7
#>    date       values jday  Month  Year myLabel fcyear
#>    <date>      <dbl> <fct> <fct> <dbl> <chr>   <fct> 
#>  1 2016-10-01   7.37 275   Oct    2016 Oct-01  2017  
#>  2 2016-10-02   5.68 276   Oct    2016 <NA>    2017  
#>  3 2016-10-03   7.90 277   Oct    2016 <NA>    2017  
#>  4 2016-10-04   8.41 278   Oct    2016 <NA>    2017  
#>  5 2016-10-05  10.6  279   Oct    2016 <NA>    2017  
#>  6 2016-10-06   7.60 280   Oct    2016 <NA>    2017  
#>  7 2016-10-07  11.1  281   Oct    2016 <NA>    2017  
#>  8 2016-10-08   9.30 282   Oct    2016 <NA>    2017  
#>  9 2016-10-09   7.08 283   Oct    2016 <NA>    2017  
#> 10 2016-10-10   8.96 284   Oct    2016 <NA>    2017  
#> # ... with 720 more rows


# Create a row number for plotting to make sure ggplot plot in
# the exact order of a fiscal year
df1 <- df %>% 
  group_by(fcyear) %>% 
  mutate(order = row_number()) %>% 
  ungroup()
df1

#> # A tibble: 730 x 8
#>    date       values jday  Month  Year myLabel fcyear order
#>    <date>      <dbl> <fct> <fct> <dbl> <chr>   <fct>  <int>
#>  1 2016-10-01   7.37 275   Oct    2016 Oct-01  2017       1
#>  2 2016-10-02   5.68 276   Oct    2016 <NA>    2017       2
#>  3 2016-10-03   7.90 277   Oct    2016 <NA>    2017       3
#>  4 2016-10-04   8.41 278   Oct    2016 <NA>    2017       4
#>  5 2016-10-05  10.6  279   Oct    2016 <NA>    2017       5
#>  6 2016-10-06   7.60 280   Oct    2016 <NA>    2017       6
#>  7 2016-10-07  11.1  281   Oct    2016 <NA>    2017       7
#>  8 2016-10-08   9.30 282   Oct    2016 <NA>    2017       8
#>  9 2016-10-09   7.08 283   Oct    2016 <NA>    2017       9
#> 10 2016-10-10   8.96 284   Oct    2016 <NA>    2017      10
#> # ... with 720 more rows

# plot with `order` as x-axis 
p2 <- ggplot(df1, 
             aes(x = order, y = values,
              color = fcyear,
              group = fcyear)) +
  geom_line() +
  theme_classic(base_size = 12) +
  xlab(NULL)

# now replace `order` label with `myLabel` created above
x_break <- df1$order[!is.na(df1$myLabel)][1:12]
x_label <- df1$myLabel[x_break]
x_label

#>  [1] "Oct-01" "Nov-01" "Dec-01" "Jan-01" "Feb-01" "Mar-01" "Apr-01"
#>  [8] "May-01" "Jun-01" "Jul-01" "Aug-01" "Sep-01"

p3 <- p2 +
  scale_x_continuous(
    breaks = x_break,
    labels = x_label) +
  theme(axis.text.x = element_text(angle = 90)) +
  scale_color_brewer("Fiscal Year", palette = "Dark2") +
  xlab(NULL)
p3
ggplotly(p3)
Slice, combine, and map fiscal year dates to calendar year dates to new column

Slice, combine, and map fiscal year dates to calendar year dates to new column


By : user1852014
Date : March 29 2020, 07:55 AM
this will help In below code, I'll assume Shortcut_Dimension_4_Code is an integer. If it's a string you can convert it or slice it like this: df['Shortcut_Dimension_4_Code'].str[:-6]. More explanations in comments alongside the code.
That should work as long as you don't have to deal with empty values.
code :
import pandas as pd
import numpy as np
from datetime import date
from dateutil.relativedelta import relativedelta

fiscal_month_offset = 6

input_df = pd.DataFrame(
    [[10225003, 2],
    [8225003, 1],
    [8225004, 3],
    [8225005, 4]],
    columns=['Shortcut_Dimension_4_Code', 'Stage_Code'])

# make a copy of input dataframe to avoid modifying it
df = input_df.copy()

# numpy will help us with numeric operations on large collections
df['fiscal_year'] = 2000 + np.floor_divide(df['Shortcut_Dimension_4_Code'], 1000000)

# loop with `apply` to create `date` objects from available columns
# day is a required field in date, so we'll just use 1
df['fiscal_date'] = df.apply(lambda row: date(row['fiscal_year'], row['Stage_Code'], 1), axis=1)

df['calendar_date'] = df['fiscal_date'] - relativedelta(months=fiscal_month_offset)
# by default python dates will be saved as Object type in pandas. You can verify with `df.info()`
# to use clever things pandas can do with dates we need co convert it
df['calendar_date'] = pd.to_datetime(df['calendar_date'])

# I would just keep date as datetime type so I could access year and month
# but to create same representation as in question, let's format it as string
df['Date'] = df['calendar_date'].dt.strftime('%m/%Y')

# copy important columns into output dataframe
output_df = df[['Shortcut_Dimension_4_Code', 'Stage_Code', 'Date']].copy()
print(output_df)
How can i get fiscal/financial year using dropdownlist in vb, the fiscal year month start at april until march

How can i get fiscal/financial year using dropdownlist in vb, the fiscal year month start at april until march


By : victoria wong
Date : March 29 2020, 07:55 AM
will be helpful for those in need After adding a Dropdown to your forms, you double click on that element. This will automatically add a new function to your code (indexchanged) and also add the mesages in the background. When you now change to a new item at runtime this event will be fired and run the source code in that new created function. There you can now get the index of the selected dropdown element the shown text or whatever you need to identify your fiscal period, abd then you can do whatever you need In the future you will notice much more events exist, depending what you need. I hope this clarifies somewhat.
Related Posts Related Posts :
  • Remove same columns from left_join
  • Grouped barplot with R
  • Strange addTaskCallback work in RStudio
  • Make dual X-axs based on different variables using ggvis
  • Generate a normal distribution within certain limits in R
  • from data table, randomly select one row per group
  • ngrams not in correct order
  • ggplot: plot title and plot overlap each other
  • Display groups with different borders in histogram with panel.superpose
  • Submit form with no submit button in rvest
  • Can't download .csv from dropbox
  • How to get Adjusted R-Square value as variable?
  • Check row-wise if element exists in comma-separated column with position
  • How to know which nodes in a graph are removable
  • Generate all possible permutations of a binary matrix
  • R, Windows and foreign language characters
  • Datapusher with missing values and mixed (srting/numeric) data
  • Adding greek characters with variables to axis title
  • R - Subsetting subsets of variable names in loops
  • R dynamically naming a data frame to be used in ggplot2
  • Optimize/vectorize a loop in R that generates randoms from ranges in input vectors?
  • How to sort groups within sorted groups?
  • How to get randomForest model output in probability using Caret?
  • how to plot a data frame into R
  • Calculate mean of a range of rows
  • How to sum the values in a text file?
  • Looking up polygons in a shapefile that point belong in... why does it work with some shapefiles, not with others?
  • R creating a list using variable columns from df
  • Determine programmatically if a function call refers to your own function or a package's (or base R's) function?
  • Adding Special Characters to a plot in R
  • How to dynamically change R .libPaths() based on hostname?
  • Print arguments of a function in R
  • Creating Variables via A loop in r
  • How to randomly divide an integer into a fixed number of integers, such that the obtained tuples are uniformly distribut
  • Remove columns using column name based on levels of factors
  • What units are the 'width = ' in geom_bar(aes = ) and position_dodge(width = ) rendered in?
  • conditional removal from data frame
  • Getting variables out of a function in R
  • Bar colors in ggplot2 geom_bar not applied correctly (R)
  • Create a vector in a dataframe based on matching a second vector to a row name in another object
  • Convert julian day to mm/dd and add year from another column in dataframe in r
  • Change SMOTE parameters inside CARET k-fold cross-validation classification
  • rollapply classes each segment the same
  • rename the header of the spliting group in r
  • Summary stats by factor level for multiple variables
  • Processing Rmarkdown inside chunks
  • R: how to join the duplicate rows in one dataframe
  • Creating a unified time-series, with dates coming from different (natural) languages
  • R error: attempt to apply non-function , randomForest , rfImpute
  • assign multiple color to each vertex in igraph
  • How do I add shading and color to the confidence intervals in ggplot 2 generated Kaplan-Meier plot?
  • How to generate spatial points with a pattern
  • ggplot2 + stat_contour variable binwidth
  • Add fake data to a data frame based on variable condition
  • Utilizing a character vector inside a function
  • match fundction with data frames that are differently constructed
  • Overlaying plots with a horizontal date in R
  • lapply'ing a for loop returns NULL
  • Dynamically add UI elements and gather their input in a dataframe in shiny
  • All the possible outcomes
  • shadow
    Privacy Policy - Terms - Contact Us © animezone.co