Q <- "
SELECT storeid, transactiontypeid, activityperiodid, sum(sales) as units, sum(Gross) as gross 
FROM fact_sales 
WHERE storeid in (535, 435, 548)
GROUP BY 1, 2, 3"

DT.libraryideas <- runQry(Q)
setkey(DT.libraryideas, activityperiodid, storeid, transactiontypeid)

if (!exists("DT.periods"))
  DT.periods <- runQry("SELECT * FROM dim_period WHERE periodid > 140")

setkeyIfNot(DT.periods, periodid)
setkeyIfNot(DT.libraryideas, activityperiodid)

DT.libraryideas[DT.periods, month := as.Date(sprintf("%s-%s-01", i.year, i.month))]
setkey(DT.libraryideas, month, transactiontypeid, storeid)

if (lunique(DT.libraryideas$transactiontypeid) == 1) {
  DT.libraryideas[, transactiontypeid := NULL]
  setkeyIfNot(DT.libraryideas, month, storeid)
}

DT.libraryideas[order(month), ma := forecast::ma(gross, 3), by=list(storeid,transactiontypeid)]
DT.libraryideas[, list(gross=mean(gross)), keyby=c(setdiff(key(DT.libraryideas), "month"))]

tit <- DT.libraryideas[storeid==548, sprintf("Library Ideas Gross Revenue\nActivity Month between %s and %s", format(min(month), "%Y-%b"), format(max(month), "%Y-%b") )]
P.lib <- 
ggplot(data=DT.libraryideas[storeid==548], aes(x=month)) + geom_point(aes(y=gross)) + geom_line(aes(y=gross), alpha=.15, size=1, color="grey50") + geom_line(aes(y=ma), size=3, color="blue") + dollar.y() + 
          labs(y="Gross\n", x="2013\n(blue line is three-month moving average)", title=tit) + 
          geom_text(data=DT.libraryideas[storeid==548], aes(x=min(month)+40, y=min(gross+100, na.rm=TRUE), label=sprintf("Total Gross\nfor period shown:\n $%s\n", formnumb(sum(gross)))), size=5)

f.out <- "~/Desktop/Library_Ideas.png"
ggsave(f.out, plot=P.lib)

.o(f.out)