This files calculates the yearly gross and net revenue figures for Spotify
and also calculates adjustments for currency

NOTE:  The currency adjustments are the "divide" factors;
The new rates in the data base BI.Currency_Neutral_Factors have "multiply" factors


library(reshape2)
DT.adj_rates <-r.d(header=TRUE, x="currency adj_rate
EUR 0.81
GBP 0.91
SEK 0.78
NOK 0.78
DKK 0.82")


cc_using <- c("US", "GB", "SE", "ES", "DE", "NO", "NL", "MX", "AU", "BR", "FR", "DK")
DT.revshare[country_code %in% cc_using, .N, keyby=list(country_code, country_name)][, country_code := factor(country_code, levels=cc_using)][order(country_code)]

DT.spotify_revenue_jan_to_july_2014_to_2015 <- DT.revshare[month(month) <= month(max(month)) & year(month) >= 2014][country_code %in% cc_using, lapply(.SD, sumn), .SDcols=c("gross_revenue", "net_revenue"), keyby=list(country_code, country_name, year=year(month), currency)]

addColsFrom_(DT.spotify_revenue_jan_to_july_2014_to_2015, DT.adj_rates, joinCols="currency")
DT.spotify_revenue_jan_to_july_2014_to_2015[, country_code := setFactorOrder(country_code, cc_using)]

DT.spotify_revenue_jan_to_july_2014_to_2015[, gross_revenue_adjusted := gross_revenue / ifelse(is.na(adj_rate), 1, adj_rate)]
DT.spotify_revenue_jan_to_july_2014_to_2015[, net_revenue_adjusted := net_revenue / ifelse(is.na(adj_rate), 1, adj_rate)]

dimCols <- c("country_code", "country_name", "currency", "adj_rate")
DT.melted <- melt(DT.spotify_revenue_jan_to_july_2014_to_2015, id.vars=c(dimCols, "year"), variable.name="revenue_type") %>% 
              dcast.data.table(makeFormula(c("revenue_type", dimCols), "year"), fun.aggregate=sumn)
DT.melted[, increase_2015_v_2014 := `2015` - `2014`]
DT.melted[, percentage_increase_2015_v_2014 := increase_2015_v_2014 / `2014`]


ll.DT_export <- 
list(
    "All data together" = DT.melted
  , "net revenue"=DT.melted[revenue_type == "net_revenue"]
  , "adjusted net revenue"=DT.melted[revenue_type == "net_revenue_adjusted"]
  , "gross revenue"=DT.melted[revenue_type == "gross_revenue"]
  , "adjusted gross revenue"=DT.melted[revenue_type == "gross_revenue_adjusted"]
  )


.myf(exportXLS)
f.out <- exportXLS.usingXLConnect(f.out=out.p("spotify_revenue_jan_to_july_2014_to_2015.xlsx"), ll.DT_export)
reveal(f.out)


denmark
australia
netherlands
brasil