Date calculation

[#1]

Hello Everyone,

I have four fields called create date(of a product) and load date(for a product), fiscal year & fiscal month. The requirement is when an User clicks a fiscal year and a fiscal month, then it should count all the products that are in the pending STAGE on that month and that fiscal year. This result is in concatenation with

the count of all the new products that are created after the selected month till today(current day). Load date is used for counting the products that are in Pending stage on the selected month & fiscal year.

For example. Say user selects June 2010(fiscal year 2011). Then count all pending stage products in June 2010. And then count all new products created after the selected month, i.e from July 2010 till current day.

Thanks & Regards,

Bikash

[chriscammers]

I must admit I am still a little fuzzy on your requirements but that is most likely not understanding your business rather than the data. I think what you have here is a relatively simple example of combining mutiple events into the same calendar. the easiest example I can think of with this is presenting Orders and Shipments, you want to select just the month and year and see a summary of all shipments and orders for the month. In your case you are looking for items that were pending and items that were created. If you look at the sample I am attaching you will see that I concatenate your pending items and your created items using different dates to define the date range for which each one will qualify. I then use "intervalmatch" to associate each row with the calendar. You'll notice that a synthetic table has been created, you can take steps to remove it but for this example it was not nessesary

[#3]

Hi, chriscammers

Thanks a lot for the sample example.

I have a question. I see that you have created a master calendar, but in my case Load Date is being used for the Fiscal Calendar, so is there any way that I can use the Load Date, instead of creating a new calendar.

Could you please also suggest, how do I load another 25 fields which has to be used for filtering the data. Fields like Tier, End Customer, Product Family, Regions etc. What do I need to modify in the existing code that you have sent.

I have a fact table called Opportunity, which has these other fields along with ITEM Name, Created Date, Load Date.

Thanks,

Bikash