## This file creates DT.mgmtr_summary_tall (misnomer) from DT.GL.summaries. ## They are essentially the same DTs, with three differences: ## (1) Some columns from DT.GL.summaries are ignored ## (2) Some blank columns added to DT.mgmtr_summary_tall, to be filled in later. Namely, ancillaryline, budget, budget_last_modified. ## (3) 'GL_gross_forDS' added as a column, whose value is simply the total_netsc_by_DSO ## DT.mgmtr_summary_tall[, GL_gross_forDS := total_netsc_by_DSO] ## ## Everything else in this file is just a bunch of checks. ## warning("Rick: Remember to add a check for NEW STORES (eg \"YouTube (BFM)\" )") { ## Begin creating the TALL DT.mgmtr_summary_tall <- { .tmp.DT <- DT.GL.summaries[, list( date = accounting_month , storeid = suppressWarnings(as.integer(storeid)) , storeid_char = storeid , store_name = store_name , music_vs_video_by_dmv = Music_vs_Video_by_DMV , ancillaryline = NA_character_ , GL_firstDate_forS = GL_firstDate_forS , GL_lastDate_forS = GL_lastDate_forS , GL_gross_forDS = Total_ThisMonth , budget = NA_real_ # , gross_is_estimate = FALSE , gross_last_modified = last_modified , budget_last_modified = as.POSIXct(NA_real_, origin=.origin.utc) ) ] rbindlist(lapply(DT.supplychain_percentages[, unique(label_sc_group)], function(x) cbind(label_sc_group=x, .tmp.DT))) } ## CONFIRM: No NAs in storeid (except for stores whose id was not matched from Great Plains) stopifnot(DT.mgmtr_summary_tall[!grepl("^GP_", storeid_char), !is.na(storeid)]) ## We may not have the SPLIT PERCS for the latest month, in which case interpolate if (max(DT.supplychain_percentages[["date"]]) < max(DT.mgmtr_summary_tall[["date"]])) { warning ("Split percentages missing for latest month(s)") tmp.lastdate.sc <- max(DT.supplychain_percentages[["date"]]) tmp.NumOfDatesMissing <- DT.mgmtr_summary_tall[date > tmp.lastdate.sc, lunique(date)] setkeyIfNot(DT.supplychain_percentages, date, verbose=FALSE, superset.ok=TRUE) DT.supplychain_percentages <- rbind( DT.supplychain_percentages ## tail(.., length()) - gives enough dates as missing. , DT.supplychain_percentages[.(tail(unique(date), tmp.NumOfDatesMissing))][, date := lubridate::`%m+%`(date, months(tmp.NumOfDatesMissing))] ) invisible(rm(tmp.lastdate.sc, tmp.NumOfDatesMissing)) } ## Add in label_sc_group and split matchKey(DT.mgmtr_summary_tall, DT.supplychain_percentages, key=kCols.date_SC, superset.ok=TRUE, verbose=FALSE) DT.mgmtr_summary_tall[DT.supplychain_percentages, `:=`( ASR_percof_DS_forDSO=i.ASR_percof_DS_forDSO, margin=i.margin )] ## UPDATE (2014-08-04 14:21:09 EDT) - do not compute DSO values yet. Do this at DT.merged level if (TRUE) # TODO: Leaving this for now, because too much code has to be changed, but remove it { ## Take a backup of the total total_netsc_by_DSO to compare later DT.mgmtr_summary_tall[, GL_gross_forDSO := GL_gross_forDS * ASR_percof_DS_forDSO] DT.mgmtr_summary_tall[, total_netsc_by_DSO := GL_gross_forDSO * margin] warning("TODO: Reminder to remove GL_gross_forDSO, total_netsc_by_DSO") } setkeyIfNot(DT.mgmtr_summary_tall, kCols.main, organize=TRUE, verbose=FALSE) DT.mgmtr_summary_tall[.(as.Date("2014-05-01"), c(1, 286))] ## UPDATE2: My 'Update' comment below is not correct. It IS useful, if we aggregate across IDs ## ## UPDATE: It turns out the DMV / Music_vs_Video indicator in DT.GL is relatively useless. ## it applies to the store as a whole. For example, all of iTunes is marked as Music. ## In fact, if we ignore those rows where total_netsc_by_DSO is $0 (possibly previously NA), ## then there is almost exactly one group per store. ## eg, can see them all: ## DT.mgmtr_summary_tall[ unique(DT.mgmtr_summary_tall[, sum(total_netsc_by_DSO) == unique(total_netsc_by_DSO[total_netsc_by_DSO!=0]), keyby=kCols.main ][ !(V1)]) ] ## ## Confirm that the total_netsc_by_DSO is unique per group, and that music_vs_video_by_dmv is unique by storeid_char "UPDATE: some values of total_netsc_by_DSO[total_netsc_by_DSO!=0] are retuning c() and now with the new version of equals(), this line is failing This doesnt matter since all of the total_netsc_by_DSO should be removed anyway. Thus, just commenting out... delete when cleaning up the code" if (FALSE) { stopifnot(DT.mgmtr_summary_tall[, equals(sum(total_netsc_by_DSO), unique(total_netsc_by_DSO[total_netsc_by_DSO!=0]), na.check=TRUE), keyby=kCols.main][, V1]) ## < SEE > DT.mgmtr_summary_tall[, equals(sum(total_netsc_by_DSO), unique(total_netsc_by_DSO[total_netsc_by_DSO!=0]), na.check=TRUE), keyby=kCols.main][!(V1)] DT.mgmtr_summary_tall[is.na(storeid) & is.na(store_name) ] } ## CONFIRM: Music_vs_Video_by_DMV is unique by each store column ## 2014-12-15 The NAs in storeid are throwing this off. But it had worked at one point, no? ## OLD 2014-12-15: stopifnot(sapply(c("storeid", "storeid_char", "store_name"), isUniqueByGroup, DT=DT.mgmtr_summary_tall, colsToCheck="music_vs_video_by_dmv")) stopifnot( isUniqueByGroup(DT=DT.mgmtr_summary_tall, colsToCheck="music_vs_video_by_dmv", "storeid_char") , isUniqueByGroup(DT=DT.mgmtr_summary_tall, colsToCheck="music_vs_video_by_dmv", "store_name") , isUniqueByGroup(DT=DT.mgmtr_summary_tall[!is.na(storeid)], colsToCheck="music_vs_video_by_dmv", "storeid") ) # ## EXPLORING: YouTube, 24/7, etc # if (FALSE) { # DT.GL[ID_greatplains=="YOUT0001" & accounting_month == "2013-01-01"] # DT.GL[store_name %like% "24/7" & accounting_month == "2013-01-01"] [, list(ID_greatplains, storepart, DMV, Music_vs_Video_by_DMV, Total_ThisMonth, store_name, storeid)] # DT.GL.summaries[store_name %like% "24/7" & accounting_month == "2013-01-01"] # } ## Calculate total total_netsc_by_DSO by date-store-supply_chain DT.mgmtr_summary_tall[, GL_gross_forDSO := GL_gross_forDS * ASR_percof_DS_forDSO] ## CONFIRM: The sum of the total_netsc_by_DSO by DSO should equal the total_netsc_by_DSO by DS stopifnot(DT.mgmtr_summary_tall[, equals(sum(GL_gross_forDSO), GL_gross_forDS[[1]]), by=kCols.datestore][, V1]) ## ALTERNATE IF ABOVE LINE FAILS if (FALSE) stopifnot(DT.mgmtr_summary_tall[date != max(date), equals(sum(GL_gross_forDSO), GL_gross_forDS[[1]]), by=kCols.datestore][, V1]) ## Confirm, once more, that there is exactly one total_netsc_by_DSO per kCols.main stopifnot(DT.mgmtr_summary_tall[, equals1(sapply(.SD, lunique)), .SDcols=c("GL_gross_forDSO", "total_netsc_by_DSO"), by=kCols.main][, V1]) stopifnot(DT.mgmtr_summary_tall[, equals1(lunique(GL_gross_forDS)), by=kCols.datestore][, V1]) #### ---- taking this part out... we add the meta info later to DT.cleaned, after splitting the subgroups ## ## ## I AM REMOVING THIS FOR NOW TO SEE HOW I WANT TO HANDLE THE MERGE ## "ADD IN META INFO" ## if (FALSE) ## { ## ## Flag whether each store is in analytics and/or accounting ## DT.mgmtr_summary_tall[ , store_is_in_analytics := (storeid %in% DT.anal$storeid) | (store_name %in% DT.anal$store_name)] ## DT.mgmtr_summary_tall[ , store_is_in_accounting := (storeid %in% DT.acc$storeid) | (store_name %in% DT.acc$store_name )] ## ## ## Bring in the bucket info per store ## matchKey(DT.mgmtr_summary_tall, DT.stores, "storeid") ## DT.mgmtr_summary_tall[DT.stores, `:=`(musicbucket=store_musicbucket, videobucket=store_videobucket) ] ## ## DT.mgmtr_summary_tall[is.na(musicbucket) | musicbucket == "zOTHER STORESz", musicbucket := "zUncategorized Store"] ## DT.mgmtr_summary_tall[is.na(videobucket) | videobucket == "zOTHER STORESz", videobucket := "zUncategorized Store"] ## } #### ---- END OF: taking this part out... we add the meta info later to DT.cleaned, after splitting the subgroups ## Ideally, everystore in OA should be in the GL and vice versa. ## However, for one reason or another, this may not be the case. ## Identify which stores in the GL are missing from OA ## Later on, we will need to estimate meta splits for those stores, based on company averages DT.mgmtr_summary_tall[, id_is_in_OA := storeid %in% c(DT.acc[date > minDateToKeep, storeid], DT.anal[date > minDateToKeep, storeid])] message("TODO: dont forget about id_is_in_OA ... bank those to a file and have someone manually check them") DT.mgmtr_summary_tall[!(id_is_in_OA), lunique(storeid)] DT.mgmtr_summary_tall[(id_is_in_OA), lunique(storeid)] invisible() } ## Check that there are not stores that are completely ZERO total_netsc_by_DSO, by storeid_char if (DT.mgmtr_summary_tall[, all(total_netsc_by_DSO == 0), keyby=storeid_char] [, sum(V1)] ) { warning(warningCols("The following storeid_char are completely $ 0.00 total_netsc_by_DSO throughout DT.mgmtr_summary_tall" , DT.mgmtr_summary_tall[, all(total_netsc_by_DSO == 0), keyby=storeid_char][(V1), storeid_char])) } ##### ______________________________________________________________________ ##### ### CHECK IF ALL STORES ARE PRESENT AND ACCOUNTED FOR #### { ## Note to self: %ni% ----> "NOT IN" stores.missing_from_GL.ll <- list() stores.missing_from_GL.ll[["ANALYTICS"]] <- merge(DT.anal[store_name %ni% DT.mgmtr_summary_tall$store_name & date >= min(DT.mgmtr_summary_tall$date), list(missing_by_store_name=TRUE), keyby=list(storeid, store_name)] , DT.anal[storeid %ni% DT.mgmtr_summary_tall$storeid & date >= min(DT.mgmtr_summary_tall$date), list(missing_by_storeid=TRUE), keyby=list(storeid, store_name)] , all=TRUE)[order(store_name)] stores.missing_from_GL.ll[["ACCOUNTING"]] <- merge(DT.acc[store_name %ni% DT.mgmtr_summary_tall$store_name & date >= min(DT.mgmtr_summary_tall$date), list(missing_by_store_name=TRUE), keyby=list(storeid, store_name)] , DT.acc[storeid %ni% DT.mgmtr_summary_tall$storeid & date >= min(DT.mgmtr_summary_tall$date), list(missing_by_storeid=TRUE), keyby=list(storeid, store_name)] , all=TRUE)[order(store_name)] for (nm in names(stores.missing_from_GL.ll)) { if (nrow(stores.missing_from_GL.ll[[nm]])) { message("\n ---------------------------------------------------------------\n", "\tThe following are present in ", nm, " and missing from the GL") print(stores.missing_from_GL.ll[[nm]]) message(" ---------------------------------------------------------------") } else message("\n ---------------------------------------------------------------\n", "\tAll accounted for in ", nm, " (relative to GL)\n ---------------------------------------------------------------\n") } } ##### ______________________________________________________________________ ##### ## TODO ... these two columns should be removed from the code altogether. "It is better to compute the breakdowns after all the percentages are calculated (ie, in/after 2c) However, I cannot simply delete the two lines of code creating them, since there are subsequent lines of code that also call these columns. I need to clean up all of the code. For now, I am leaving them as is, and just dropping them here at the end " ## TEMP: This should be removed earlier DT.mgmtr_summary_tall[, c("total_netsc_by_DSO", "GL_gross_forDSO") := NULL] ##### ______________________________________________________________________ ##### saveImageTo(subProj="Before_2c")