Re: qry-SalesAnalysysCollectData?

From: Maureen (nosend2me_at_hotmail.com)
Date: 05/30/04 

Date: Sun, 30 May 2004 14:11:34 -0400

Gary,

Thanks for your reply. I located a post from Duanes on the topic. It looks
like his suggestion does YTD... I suspect this is a calendar year. What I
want is a 'rolling' sales history for each SKU and Customer that will
provide a comparison of sales for the last 366 day compared to the 366 days
prior rather than YTD.

M

"Gary Walter" <garylwpleasenospam@wamego.net> wrote in message
news:eiG1kbkREHA.1448@TK2MSFTNGP11.phx.gbl...
>
> "Maureen" wrote
> > Gary,
> >
> > Thank for your post. I understand the basics and have the six queries
done
> > for inventory and customer with all of them collected into a master to
drive
> > my report. I thought that perhaps there was a simpler way... I guess
not.
> >
> Hi Maureen,
>
> Did you see Duane's response to
> "Current Date, MTD, YTD, Last Year's MTD"?
>
> Possibly you could use the same method for
>
> M-last 30 days ((t1.InvDate)>=Date()-30)
> Q-last 90 days ((t1.InvDate)>=Date()-90)
> X-last 180 days ((t1.InvDate)>=Date()-180)
> Y-last 366 days ((t1.InvDate)>=Date()-366)
> L-Between day 366 and 732 ((t1.InvDate)>=Date()-732
> And (t1.InvDate)<Date() -
365)
> Z-Life to date
>
> He used the Abs value of date test multiplied
> by Qty and sums that result.
>
> for example:
>
> SELECT
> t2.SKU,
> SUM(NZ(t1.Qty)) AS Z
> SUM(NZ((t1.Qty)*Abs(((t1.InvDate)>=Date()-30))) AS M
> {etc., I did not check parentheses above}
>
> FROM
> tblInventory AS t2
> LEFT JOIN
> tblInvoiceDetail AS t1
> ON t2.SKU = t1.SKU
> GROUP BY t2.SKU
> ORDER BY t2.SKU;
>
>
>
>
>

Relevant Pages

  • RE: Vlookup or Getpivotdata or ?
    ... I have pulled in some data that has customer id, customer name, ship to ... location and value of orders in total by segment for both last ... Last years information (outside the parameters of the pivot table) I've ... YTD current and pivoted the data in the same layout. ...
    (microsoft.public.excel.misc)
  • Re: MDX problem
    ... The problem is the nonemptycrossjoin(). ... get issues like this with it not recognising non empty calculations. ... Rows: Customer then Product ... customer in the rows showing 0 for YTD and whatever value for Last Yr ...
    (microsoft.public.sqlserver.olap)
  • Question on YTD
    ... Here my problem, I have a customer who want to have YTD on columns, and ... columns from sales ... Best regards, ...
    (microsoft.public.sqlserver.olap)
  • MDX problem
    ... I currently have a requirement to show all sales for customers in 2006 ... Rows: Customer then Product ... customer in the rows showing 0 for YTD and whatever value for Last Yr ... SET [Report Measures] as ...
    (microsoft.public.sqlserver.olap)