## GL_pt2_e Create DT.cleaned.r ## NOTE TO SELF " I will call this 'DT.cleaned' for now, to match what I had previously done. TODO: Give this a better name and clean the subsequent files " ## DT.cleaned is simply the rbind (With fill) of ## DT.merged ## DT.ancillary_from_rev_summary (with colname changed) ## DT.adjustments (filtering to non-zero adjustments) DT.cleaned <- rbind( DT.merged , DT.ancillary_with_forecasts , DT.adjustments[get(colName_being_adjusted) != 0] , use.names=TRUE , fill=TRUE ) ## There should not be any columns in DT.cleaned that did not already exist in DT.merged if (length(wh <- setdiff(names(DT.cleaned), names(DT.merged)))) stop (warningCols("The following columns are in DT.cleaned but not DT.merged (HINT: check what was rbind into DT.cleaned)", wh, cols=5)) ## Add in store_musicsuperbucket where 'Streaming' and 'Other Services' are combined into one group ## DO NOT aggregate this, as we still may want to break it down addSuperBucket_(DT.cleaned) ## ----------------------------------------------------------------------------------------- ### ## CONFIRM VALUES ## (Calculated values vs. Reported Values) ## ----------------------------------------------------------------------------------------- ### ## Three separate checks: ## against the total company-wide, bottom-line revenue (net of SC AND including ancillaryline) ## against the total company-wide, main revenue (net of SC but *NO* ancillary lines) ## against the monthly total for group breakdowns by date-OSC-Ancillary (no store, no meta) ## ## NOTE: Not breaking down by meta, since we do not have reporting at that level. ## ----------------------------------------------------------------------------------------- ### { ## Monthly Total, for the entire company bottom line { tmp_DT.monthly_sums <- DT.cleaned[date <= lastDateClosed, list(calculated = sum(GL_net_forDSOM)), keyby=kCols.date] calc.tol <- max(merge(DT.Total_Reported_Revenue_By_Month, tmp_DT.monthly_sums, by=kCols.date, all=TRUE)[, abs(reported_gross - calculated)], na.rm=TRUE) message(" Monthly totals for bottom line revenues are accurate to $", fw3(calc.tol)) if (calc.tol > 10) stop("The difference in monthly totals between what was calculated and the correct, as reported, values\n is more than a rounding error (ie larger than a $10 difference for a month)") } ## Monthly Total, for the entire company bottom line { tmp_DT.monthly_sums_calc <- DT.cleaned[date <= lastDateClosed & is.na(ancillaryline), list(calculated = sum(GL_net_forDSOM)), keyby=kCols.date] tmp_DT.monthly_sums_rep <- DT.mainline_group_subtotals_from_rev_summary[, list(reported_gross = sum(reported_gross)), keyby=kCols.date] calc.tol <- max(merge(tmp_DT.monthly_sums_rep, tmp_DT.monthly_sums_calc, by=kCols.date, all=TRUE)[, abs(reported_gross - calculated)], na.rm=TRUE) message(" Monthly totals for non-ancillary revenues are accurate to $", fw3(calc.tol)) if (calc.tol > 10) stop("The difference in monthly totals between what was calculated and the correct, as reported, values\n is more than a rounding error (ie larger than a $10 difference for a month)") } { byCols <- c("date", "ancillaryline", "label_sc_group") tmp_DT.monthly_sums_by_grp <- DT.cleaned[date <= lastDateClosed][][label_sc_group %ni% c("Orchard", "RED"), label_sc_group := "OSC"][, list(calculated = sum(GL_net_forDSOM)), keyby=byCols] tmp_DT.reported_totals_by_grp <- DT.rev_summary.molt[, list(reported_gross = sumn(reported_gross)), keyby=byCols] tmp_DT.grp <- merge(tmp_DT.monthly_sums_by_grp, tmp_DT.reported_totals_by_grp, all=TRUE) ## CONFIRM: There should be no NAs stopifnot(tmp_DT.grp[, !(is.na(reported_gross) | is.na(calculated))]) calc.tol <- max(tmp_DT.grp[, abs(reported_gross - calculated)]) message(" Monthly totals for OSC/Ancillary revenues are accurate to $", fw3(calc.tol)) if (calc.tol > 10) stop("The difference in group totals between what was calculated and the correct, as reported, values\n is more than a rounding error (ie larger than a $10 difference for a month)") } } ## ----------------------------------------------------------------------------------------- ### ## Create a similar table where all of non-red supply chain is treated as OSC { OSC_values <- DT.cleaned[, sort(unique(setdiff(label_sc_group, c("RED", "Orchard"))))] ## CONFIRM: For gross_last_modified and budget_last_modified to ensure there is only one per OSC stopifnot(DT.cleaned[label_sc_group %in% OSC_values, lapply(.SD, function(x) lunique(x) == 1), by=kCols.datestore, .SD=c("gross_last_modified", "budget_last_modified")][, gross_last_modified & budget_last_modified]) oscCols.notUsing <- c("SHOULD_BE_0", "ASR_percof_DS_forDSO", "margin", "OA_firstDate_forSOM", "GL_firstDate_forSO", "date_is_too_early", "date_is_outside_GL_forS", "DSOM_OA_needs_fill", "OA_percof_DSO_forDSOM", forecastColsToBringIn) oscCols.meta <- c(kCols.smry, "id_is_in_GL", "id_is_in_OA", "missing_from_OA", "GL_firstDate_forS", "GL_lastDate_forS", "OA_firstDate_forS" , "storeid_char", "music_vs_video_by_dmv" , "GL_ZeroMonth_forDS", "OA_ZeroEntirely_forS", "OA_ZeroMonth_forDS" , "GL_gross_forDS" ## IMPORTANTLY: budget is a meta, not a summing, but might change , "budget" ## Need to confirm these , "gross_last_modified", "budget_last_modified") oscCols.summing <- c( "GL_net_forDSOM" , "GL_gross_forDSOM", "OA_gross_forDSOM" ## Will try the percentages too , "OA_percof_DS_forDSOM" ## NOTE: percof_DS_ is okay, but not percof_DSO_ ) ## Check that none where missed oscCols.missed <- setdiff(names(DT.cleaned), c(oscCols.notUsing, oscCols.meta, oscCols.summing)) if (length(oscCols.missed)) { warning(warningCols("missed some columns from DT.cleaned when going to DT.cleaned.OSC : ", oscCols.missed)) } # no : ## there are expected NAs in OA_gross_forDSOM, stemming from missing_from_OA # no : ## I considered zeroing them out in DT.cleaned, but we want them to be NULL in the final DB # no : ## Thus, just zero them here so that the test condition passes # no : ## # no : ## On second thought, if the OA_percof_DS_forDSOM is 0, then zero out the NA # no : DT.cleaned[is.na(OA_gross_forDSOM) & !is.na(GL_net_forDSOM) & equals0(OA_percof_DS_forDSOM), OA_gross_forDSOM := 0] ## CONFIRM: within each oscCols.meta - less label_sc_group - group, the NAs in the OSC columns shuold be all or none -- EXCEPT for OA_gross_forDSOM, which has NAs from missing_from_OA ## Meaning, it should NOT be the case where some OSC columns have an NA and others do not. ## ## If this fails, then we need to decided how to treat (X + NA) ? Is it X or NA ? { temp_DT.confirm <- DT.cleaned[label_sc_group %in% OSC_values , lapply(.SD, function(x) all(is.na(x))), .SD=oscCols.summing, by=oscCols.meta ] stopifnot(temp_DT.confirm[ , lapply(.SD, function(x) all(x) || !any(x)), .SD=oscCols.summing, by=c(setdiff(oscCols.meta, "label_sc_group")) ][, apply(.SD, 1, all), .SDcols=c(setdiff(oscCols.summing, "OA_gross_forDSOM"))] ) } tol.forperc <- 5e-4 ## NOTE TO SELF on usig sum() or sumn() ## I ran both and confirmed that any NAs that remain when using sum() are 0 when using sumn() ## meaning that when an NA is in a group, the whole group is NA (or 0) ## However, using sumn() as it is safer in the case that the group is only partially NA ## DT.cleaned.OSC <- DT.cleaned[TRUE][label_sc_group %in% OSC_values, label_sc_group := "OSC"][, lapply(.SD, sumn), keyby=oscCols.meta, .SDcols=oscCols.summing] ## If a group was a one-row'er, the OA_percof_DS_forDSOM will be length(OSC_values) ## Set these to 1 DT.cleaned.OSC[equals(OA_percof_DS_forDSOM, length(OSC_values), tol=1e-2 ), OA_percof_DS_forDSOM := 1] ## CONFIRM: No OA_percof_DSO_forDSOM greater than 1 stopifnot(DT.cleaned.OSC[, OA_percof_DS_forDSOM <= 1 ]) ## CONFIRM: Sum to 1 sumsToOne(DT.cleaned.OSC[OA_percof_DS_forDSOM != 0], col="OA_percof_DS_forDSOM", by=kCols.datestore , tol=tol.forperc) } ## ALSO TAKE THE BUDGET WHILE MERGING. ## overwrite the current budget which is all zero ## Create DT.cleaned.aggd (from DT.cleaned.OSC & DT.manual) ## DT.manual has the store info for which stores will be shown { DT.cleaned.aggd <- copy(DT.cleaned.OSC) BackUpOrRestore("DT.cleaned.aggd", clear=TRUE) ## Clear out the store names & ids for the non-MgmtReport stores { ## Most stores will be aggregated out, except for a few handpicked stores matchKey(DT.cleaned.aggd, DT.manual, kCols.store, organize=TRUE) ## CONFIRM THERE ARE ROWS IN THE MERGE stopifnot(nrow(merge(DT.cleaned.aggd, DT.manual, all=FALSE, allow=TRUE)) > 0) ## Only those stores specifically in DT.manual will be agg'd individually. The rest will be grouped together DT.cleaned.aggd[DT.manual, `:=`(storeid_agg=storeid, store_name_agg=store_name), allow=TRUE] DT.cleaned.aggd[is.na(storeid_agg), storeid_agg := -1] DT.cleaned.aggd[is.na(store_name_agg), store_name_agg := "OtherStores"] ## replace the original storeid, store_name with the agg'd version, drop the agg'd DT.cleaned.aggd[, storeid := storeid_agg ][ , storeid_agg := NULL] DT.cleaned.aggd[, store_name := store_name_agg ][ , store_name_agg := NULL] ## Aggregate out the non-Mgmt Stores DT.cleaned.aggd <- DT.cleaned.aggd[, lapply(.SD, sumn), keyby=c(kCols.smry, "budget"), .SDcols=oscCols.summing] ## The percentages no longer hold. Drop them DT.cleaned.aggd[, OA_percof_DS_forDSOM := NULL] } ## Copy in the budget figures, taken from DT.manual { BackUpOrRestore("DT.cleaned.aggd") ## Convert OSC values, and then aggregate the budget tmp_DT.budget <- DT.manual[TRUE][label_sc_group %in% OSC_values, label_sc_group := "OSC"][, list(budget=sumn(budget)), keyby=kCols.smry] ## CONFIRM, budgets add up stopifnot(equals(DT.manual[, sumn(budget), keyby=kCols.date], tmp_DT.budget[, sumn(budget), keyby=kCols.date], tol=1e-2)) ## Most stores will be aggregated out, except for a few handpicked stores matchKey(DT.cleaned.aggd, tmp_DT.budget, kCols.datestore, organize=TRUE) ## The merge with tmp_DT.budget will introduce NAs. ## For the oscCols.summing columns, we will zero these out. ## First confirm that there are no NAs before the merge stopifnot(DT.cleaned.aggd[, !is.na(.SD), .SDcols = c(setdiff(oscCols.summing, "OA_percof_DS_forDSOM"))]) ## DROP The budget column from DT.cleaned.aggd, since we will be importing the column from tmp_DT.budget ## CONFIRM current budget is all zero stopifnot(DT.cleaned.aggd[, is.naor0(budget)]) DT.cleaned.aggd[, budget := NULL] DT.cleaned.aggd <- merge(DT.cleaned.aggd, tmp_DT.budget, by=kCols.smry, all=TRUE) ## Clean up the NAs for (.cc in c(setdiff(oscCols.summing, "OA_percof_DS_forDSOM"))) DT.cleaned.aggd[is.na(get(.cc)), (.cc) := 0 ] stopifnot(DT.cleaned.aggd[, lunique(budget)==1, keyby=kCols.smry][, V1]) ## Confirm that the budgets total up. { ## FOR the checks that do NOT use label_sc_group, use tmp_DT.budget to check. ## all the rest use DT.manual ## (ancillaryline is the only column with NAs in it, hence removing) ## Check by summary columns stopifnot(equals( DT.cleaned.aggd[, sumn(budget), keyby=kCols.smry][V1 != 0] [, !"ancillaryline", with=FALSE] , tmp_DT.budget[, sumn(budget), keyby=kCols.smry] [V1 != 0] [, !"ancillaryline", with=FALSE] )) ## Check by date-store stopifnot(equals( DT.cleaned.aggd[, sumn(budget), keyby=kCols.datestore][V1 != 0] , DT.manual[, sumn(budget), keyby=kCols.datestore] [V1 != 0] )) ## Check by date stopifnot(equals( DT.cleaned.aggd[, sumn(budget), keyby=kCols.datestore][V1 != 0] , DT.manual[, sumn(budget), keyby=kCols.datestore] [V1 != 0] )) } # stopifnot(DT.cleaned.aggd[!is.na(budget), !equals0(sumn(GL_net_forDSOM)) & !is.na(GL_net_forDSOM), by=kCols.datestore][, V1]) # ## Calculate the budget per DSOM. Save it to a new column so that we can confirm values are correct, then copy over and delete the extra column # DT.cleaned.aggd[, budget2 := budget * GL_net_forDSOM / sumn(GL_net_forDSOM), by=kCols.datestore] # ## CONFIRM # stopifnot(equals(DT.cleaned.aggd[date == lastDateClosed, sumn(budget2)], DT.manual[date == lastDateClosed, sumn(budget)])) # stopifnot(DT.cleaned.aggd[, equals(sum(budget2), unique(budget)) | (is.na(budget2) & is.na(budget)), keyby=kCols.datestore][, V1]) # ## COPY OVER # DT.cleaned.aggd[, budget := budget2][, budget2 := NULL] # stopifnot(DT.cleaned.aggd[.(Aug)][, sumn(budget)] < 20) # 5027958 # 2000000 } } # -------------------- # ## RECONFIRM THE TOTOALS: # -------------------- # ## Monthly Total, for the entire company bottom line { tmp_DT.monthly_sums <- DT.cleaned.aggd[date <= lastDateClosed, list(calculated = sum(GL_net_forDSOM)), keyby=kCols.date] calc.tol1 <- max(merge(DT.Total_Reported_Revenue_By_Month, tmp_DT.monthly_sums, by=kCols.date, all=TRUE)[, abs(reported_gross - calculated)], na.rm=TRUE) stopifnot(calc.tol1 < 1) } ## Monthly Total, for the entire company bottom line { tmp_DT.monthly_sums_calc <- DT.cleaned.aggd[date <= lastDateClosed & is.na(ancillaryline), list(calculated = sum(GL_net_forDSOM)), keyby=kCols.date] tmp_DT.monthly_sums_rep <- DT.mainline_group_subtotals_from_rev_summary[, list(reported_gross = sum(reported_gross)), keyby=kCols.date] calc.tol2 <- max(merge(tmp_DT.monthly_sums_rep, tmp_DT.monthly_sums_calc, by=kCols.date, all=TRUE)[, abs(reported_gross - calculated)], na.rm=TRUE) stopifnot(calc.tol2 < 1) } { byCols <- c("date", "ancillaryline", "label_sc_group") tmp_DT.monthly_sums_by_grp <- DT.cleaned.aggd[date <= lastDateClosed][][label_sc_group %ni% c("Orchard", "RED"), label_sc_group := "OSC"][, list(calculated = sum(GL_net_forDSOM)), keyby=byCols] tmp_DT.reported_totals_by_grp <- DT.rev_summary.molt[, list(reported_gross = sumn(reported_gross)), keyby=byCols] tmp_DT.grp <- merge(tmp_DT.monthly_sums_by_grp, tmp_DT.reported_totals_by_grp, all=TRUE) ## CONFIRM: There should be no NAs stopifnot(tmp_DT.grp[, !(is.na(reported_gross) | is.na(calculated))]) calc.tol3 <- max(tmp_DT.grp[, abs(reported_gross - calculated)]) stopifnot(calc.tol3 < 0.5) } cleanBuckets_ (DT.cleaned.aggd) DT.cleaned.aggd <- copy(DT.cleaned.aggd) DT.cleaned.aggd[, store_musicsuperbucket := copy(store_musicsuperbucket)] DT.cleaned.aggd[, store_musicbucket := copy(store_musicbucket)] ## CONFIRM THE Superbucket if ("store_musicsuperbucket" %in% names(DT.cleaned.aggd)) { stopifnot(DT.cleaned.aggd[(store_musicsuperbucket == "Streaming & other services"), store_musicbucket %in% c("Streaming", "Other Services")]) stopifnot(DT.cleaned.aggd[(store_musicsuperbucket == "Download (w. Physical)"), store_musicbucket %in% c("Download", "Physical")]) stopifnot(DT.cleaned.aggd[store_musicsuperbucket %ni% c("Download (w. Physical)", "Streaming & other services"), store_musicbucket == store_musicsuperbucket]) } ### ---- FORECASTS ------ ### if (RunIsAForecast()) { DT.net_ratios <- DT.cleaned[date %in% DT.GL_forecasted$date, list(net_to_gross_ratio=GL_net_forDSOM / GL_gross_forDS), keyby = kCols.smry] # merge(DT.GL_forecasted, DT.net_ratios, by=kCols.datestore, all=TRUE, allow=TRUE) DT.GL_forecasted.merged <- merge(DT.GL_forecasted, DT.net_ratios, by=kCols.datestore, all=TRUE, allow=TRUE) forecastCols <- c("forecasted_value", "forecasted_lo80", "forecasted_lo95", "forecasted_hi80") for (col in forecastCols) DT.GL_forecasted.merged[, paste0("net_", col) := get(col) * net_to_gross_ratio] DT.GL_forecasted.merged[, (forecastCols) := NULL] jesusForData_Forecasting(DT.GL_forecasted.merged) if (exists("FORECASTS")) FORECASTS[as.character(fakeToday)] <- list(DT.GL_forecasted) } ### ---- FORECASTS ------ ### ## Cleanup: remove all tmp_DT 's suppressWarnings({ rm( tmp_DT.grp ) rm( tmp_DT.monthly_sums ) rm( tmp_DT.monthly_sums_by_grp ) rm( tmp_DT.reported_totals_by_grp ) rm( calc.tol ) }) BackUpOrRestore("DT.cleaned.aggd", clear=TRUE)