Passing fields as array from Query, is it possible to print field names also?

[fredalina]

I am writing code to determine the average sales over a 12-month period, with one field per month (derived via crosstab). However, some items were only made available for sale at some point inside the 12-month period, and thus we only have a few months of sales data for those items. We don't want to average all 12 months' sales, including zeros, but would instead need to start in the month following the item's creation date. My array consists of the 12 sales months and the last is the item's creation date.

My function in the query is called like this:

Average Monthly Demand: AvgDem([200811],[200812],[200901],[200902],[200903],[200904],[200905],[200906],[200907],[200908],[200909],[200910],[creatdt])

Code so far follows:

last = UBound(FieldArray)
        creation = FieldArray(last)
        creatmonth = Right(creation, 2)
        creatyear = Left(creation, 4)
 
        'Determine the month following the item creation
        
        Select Case creatmonth
            Case 12
                newmonth = 1
                newyear = creatyear + 1
            Case Else
                newmonth = creatmonth + 1
                newyear = newyear
        End Select
        
        startper = newyear & newmonth

At this point, I have determined the name of the period (the field name, essentially) at which to start the average. I have the 12 periods' sales data, but need to know the syntax of how to determine the name of the field for comparison.

In other words, if the create date for an item was 200905, then I will need to average the sales in 200906, 200907, 200908, 200909, and 200910. 5 periods instead of all 12. I'm not sure how to determine which I in FieldArray(I) to begin my For/Next loop that will sum the values in.

(I know how to determine the average once the start month is determined, but I feel like I'm missing a code word for the field name. I also know that there's a possibility that I will have to make changes to the month if it is less than 10 -- add in a zero, etc, and that shouldn't be a problem.)

Many thanks!

 

[jdraw]

I am writing code to determine the average sales over a 12-month period, with one field per month (derived via crosstab). However, some items were only made available for sale at some point inside the 12-month period, and thus we only have a few months of sales data for those items. We don't want to average all 12 months' sales, including zeros, but would instead need to start in the month following the item's creation date. My array consists of the 12 sales months and the last is the item's creation date.

My function in the query is called like this:

Average Monthly Demand: AvgDem([200811],[200812],[200901],[200902],[200903],[200904],[200905],[200906],[200907],[200908],[200909],[200910],[creatdt])

Code so far follows:

last = UBound(FieldArray)
        creation = FieldArray(last)
        creatmonth = Right(creation, 2)
        creatyear = Left(creation, 4)
 
        'Determine the month following the item creation
        
        Select Case creatmonth
            Case 12
                newmonth = 1
                newyear = creatyear + 1
            Case Else
                newmonth = creatmonth + 1
                newyear = newyear
        End Select
        
        startper = newyear & newmonth

At this point, I have determined the name of the period (the field name, essentially) at which to start the average. I have the 12 periods' sales data, but need to know the syntax of how to determine the name of the field for comparison.

In other words, if the create date for an item was 200905, then I will need to average the sales in 200906, 200907, 200908, 200909, and 200910. 5 periods instead of all 12. I'm not sure how to determine which I in FieldArray(I) to begin my For/Next loop that will sum the values in.

(I know how to determine the average once the start month is determined, but I feel like I'm missing a code word for the field name. I also know that there's a possibility that I will have to make changes to the month if it is less than 10 -- add in a zero, etc, and that shouldn't be a problem.)

Many thanks!

I'm not following your issue.
If item was created in 200905, why would you only average over 5 months?

Why would you not average over 06,07,08,09,10,11,12?
Now I know we're only in 200911, at the moment--is that why you're limiting the averaging period?

Seems to me you have the creatmonth correct, but you start averaging in creatmonth+1

 

[Rich]

Why you need an array and don't you use real dates?

 

[fredalina]

It should be creatmonth + 1 because items are added all through the month, so i only want to include the first complete month of sales instead of having a perhaps incomplete month in the average. I am excluding the creatmonth and previous months from the 12-month sales average because it will skew the sales down. Also we won't be using 200911 data yet as it's not a full month, so we're left with 5 months of sales. As we advance time, as I run this again, we will have additional months of sales to include in this example.

I don't want to hard-code the months because i may use this code again in a few months (in fact i know i will), so i'd like to be able to just re-run the query which calls the code; if I hard-code the months, i will have to modify the code every time i run this query. At that time, the "new" items will have 3 additional months-worth of sales data for the 12-month (rolling) average.

It might help clarify things if I mention that some items are seasonal type items. Example: We sell more rakes during Autumn and more snow boots during Winter. In those cases, for items that are not brand new offerings, we do want the full 12-months' worth of sales data averaged out for a monthly average. Which is entirely different than if an item was just added this autumn but is not a seasonal type good, say it's writing pens, and any one month's sales should be comparable to any other month's sales, so we would only want the months since the item was offered. Is that making sense?

Where I'm stuck is that i know the field name/month to begin the averaging; in my original example it would be 200906. However, I am only sending the values to the code, not the field names. I don't know which value corresponds to this month. Is there a way to either send field names or identify them. I know this isn't the code, but it feels like there should be a word that I could use, i.e.:

[B]Name[/B].FieldArray(I)

where I'm looking for the correct bolded word. Or maybe

[B]Name[/B](FieldArray(I))

similar to

UBound(FieldArray)

returns the last value in the FieldArray array. Is there a word that "prints" the NAME of the field?
Or a way to pass two arrays, one with the names and one with the values?

 

[fredalina]

I think this is enough ..... I do agree with you. Those are the most effective way

I'm sorry; I don't understand this?

 

[Rich]

I'm sorry; I don't understand this?

It's spam just ignore it, again I ask why are you using an array to do this, a single pivotcrosstab will do it

 

[fredalina]

It's spam just ignore it, again I ask why are you using an array to do this, a single pivotcrosstab will do it

i don't see how a single pivotcrosstab would do it or i would have implemented this first. Could you please explain more what you mean?

Using sales data by month obtained from a previous crosstab query for several items, I need to come up with an average monthly sales figure. This is simple for items which have been on the shelves for longer than 12 months, but for items which are new, we only want to average those full months that have occurred since the item's creation date. I can't simply average months > 0 because some items which have been on the shelves longer than 12 months also have zero sales during non-seasonal months.

I'm stuck how to determine this information other than to determine the first month to consider (creatdate + 1 for new items, or 12 full months for older items) and then to average those months via code.

Thanks!