setScience(proj="Accounting_Date", create=TRUE, subl=FALSE, load=FALSE) lib(ggplot, quietly=TRUE) lib(reshape2, quietly=TRUE) plotting <- FALSE maxMonthsApart <- 9 percLimit <- .80 ## ------------------------------------------------------ DT.stores <- get_dim_store() colsToPull <- c("accounting_month", "activity_month", "storeid" , USA="country_name='USA'", label_is_red="label_is_red" , transac="transac_type", transac_abbr="transac_type_abbr" , "country_name") selfname_(colsToPull, only=TRUE) measureCols <- c("gross", "units", "rows") kCols.main <- c("storeid", "is_usa_red", "activity_month") kCols <- c(kCols.main, setdiff(names(colsToPull), c(kCols.main, "USA", "activity_month")) ) ## ------ EXECUTE QUERY DT.usa_red <- runQry(makeQry(tbl="accounting", schema="bi" , colsToPull=colsToPull , colsToAgg=c("gross", "units") , colsWithaggFunc = c(rows="count(*)") , minDate="2013-06-01" , dateCol="activity_month" # , where=list(storeid = c(1, 187, 286)) , where="store_is_in_analytics" )) DT.usa_red[, lapply(.SD, range), .SDcols=getDateColNames(DT.usa_red, loose=TRUE)] DT.usa_red[accounting_month == activity_month, .N, by=c("storeid", "usa", "label_is_red")] DT.usa_red[accounting_month == activity_month, .N, by=c("storeid", "usa", "label_is_red")] DT.usa_red[, monthsApart := monthsDiff(accounting_month, activity_month)] DT.usa_red[, is_usa_red := usa & label_is_red] ## Aggregate the measure cols DT.usa_red.agg <- DT.usa_red[, lapply(.SD, sum), keyby=c(kCols, "monthsApart"), .SDcols=measureCols] ## calculate percentages DT.usa_red.agg[, sprintf("perc.%s", measureCols) := lapply(.SD, function(x) x / sum(x)) , .SDcols=measureCols , keyby=kCols] ## We use GROSS because GROSS is what we are after. The other columns are for comparisons ## only keep rows that represent more than 2% of gross. DT.usa_red.agg <- DT.usa_red.agg[!equals0(perc.gross, tol=.02)] DT.usa_red.agg setkey(DT.stores, "storeid", verbose=FALSE) DT.usa_red.agg[DT.stores, store_name_short := store_name_short] DT.usa_red.agg[, store := sprintf("%s - %3i", store_name_short, storeid)] jesusForData(DT.usa_red) jesusForData(DT.usa_red.agg) if (.Pfm == "Darwin") { .g() loadFromJesus("DT.usa_red", over=TRUE) loadFromJesus("DT.usa_red.agg", over=TRUE) } if (plotting) { DT.plot <- copy(DT.usa_red.agg) DT.plot[, monthsApart := factor(monthsApart, levels=seq(maxMonthsApart+1)-1)] DT.plot[, store := factor(store)] DT.plot[, c("perc.units", "perc.rows") := NULL] ## Clean up for output DT.plot[, Transac := sprintf("%s: %s", transac_abbr, transac)] DT.plot[, USA_RED := ifelse(is_usa_red, "USA+Red", "ALL OTHER")] P.usa_red_for_analytics_stores <- { ggplot(DT.plot) + aes(x=monthsApart, y=perc.gross, color=store) + geom_line(aes(group=accounting_month), size=1.15) + geom_vline(xintercept=2, color="dark blue", size=2 , linetype="dashed", alpha=.5) + geom_point(size=2) + percent.y() + labs(title="Stores in Analytics", x="Months Apart" , y="Percent of Total Gross by Accounting Month") + facet_grid(store + USA_RED ~ .) } f.out.plot <- ggsave.out(plot=P.usa_red_for_analytics_stores, open=TRUE, height=25) dev.off() ## --------------------------- ## stores <- unique(DT.plot, by="store")[, setNames(nm=store_name_short, store)] f.out.storeplots <- emptylist(stores) setkeyIfNot(DT.plot, store, country_name, transac) ## Sample for testing s <- stores["Amazon"] f.out.storeplots <- lapply(stores, function(s) { message("processing: ", s) P <- { ggplot(DT.plot[.(s)]) + aes(x=monthsApart, y=perc.gross, color=Transac) + geom_line(aes(group=accounting_month), size=1.15) + # geom_vline(xintercept=2, color="dark blue", size=2 # , linetype="dashed", alpha=.5) + geom_point(size=2) + percent.y() + labs(title=s, x="Months Apart" , y="Percent of Total Gross by Accounting Month") + facet_grid(transac + country_name ~ .) } nm <- sprintf("Months Diffs by Country, Transac - %s", names(stores)[stores == s]) ## 1 inch per row height = 1 * unique(DT.plot[.(stores["iTunes"])], by=c("store", "transac", "country_name"))[, .N] ggsave.out(plot=P, nm=nm, open=FALSE, height=25) }) .o(out.p()) } DT.usa_red.agg[.(1)] --- WAIT ----- I am given activity_month and I want to find accounting_month ----- I am going in the wrong direction ## ANYTHING OVER 84% is considered "whole" percLimit <- validPercentage(percLimit, min=0, max=1) DT.accounting_month_diff <- copy(DT.usa_red.agg) DT.accounting_month_diff[, perc.using := {if (any(wh <- perc.gross > percLimit)) {perc.gross[wh] <- 1; perc.gross[!wh] <- 0}; perc.gross}, by=kCols] DT.accounting_month_diff <- DT.accounting_month_diff[perc.using != 0] ## Drop all of the columns not needed for finding the accounting month diff colsDropping <- c("transac", "is_usa_red", measureCols, paste0("perc.", measureCols), "store", "store_name_short") suppressWarnings(DT.accounting_month_diff[, c(colsDropping) := NULL]) kCols.monthsapart <- c("storeid", "label_is_red", "transac_abbr", "country_name") setkeyIfNot(DT.accounting_month_diff, kCols.monthsapart) ## Count the average months apart for each store. It shold be relatively constant, except for flukes ## Actally, I cannot just take mean by store acorss all ROWS. What if a delayed month had many diffferent territories ## I need to first take a mean for the month DT.accounting_month_diff[, mean_months.byStore := mean(monthsApart), by=storeid] DT.accounting_month_diff DT.accounting_month_diff[, mean(monthsApart), keyby=kCols.monthsapart][V1 < 1] DT.accounting_month_diff[, round(mean(monthsApart), 0), keyby=c(setdiff(kCols.monthsapart, "label_is_red"))][storeid == 27][order(transac_abbr, V1)] DT.usa_red[transac_abbr == "TD" & storeid == 27] Now ... the plots dont matter so much ... what matters is the column "expected accounting month" in bi.analytics FEED IT!