Monthly Buckets

[UNCTarheels]

I am reading a file that I need to extract Current Period, and the previous 12 monthly periods. I have the transaction date, so I know that I can key off the year and the month. I am creating a CurrentDate field, where this date is the the previous date, such as yesterday, unless it is Monday, Sunday, or Saturday, then I default to the previous Friday's date.

I am thinking that I need 13 formula fields to handle this, as an additional requirement is that I need to only show a line where the previous two month periods show a decline in Qty. For example, Qty sold in October was 5, Qty Sold in Sept is 25, and Qty Sold in Aug is 28. I would want to show this as from Aug to Sept, there was a decrease in Qty sold.

I am thinking that the formula should be:

If Year ({IM5_TransactionDetail.TransactionDate}) = Year ({@CurrentDate}) Then If Month ({IM5_TransactionDetail.TransactionDate}) = Month ({@CurrentDate}) Then {@TransQty}

This will give me the qty sold for the current period. I have to use a TransQty field as the Qty is inversed in the file.

As for the previous fields, I will do dateadd to the fields to determine the correct months.

Does this look okay? A little bit you, a little bit me. [hippie]