Complex Reports

[ashok1203]

Hi All,

Within this sheet, there will be a container and within the container 4 reports.

1st Report = Expired Inventory = with a condition Exp Date < the date of the month selected. (e.g. if you selected Aug 2016, then where an inventory line has Exp date < 1st Aug 2016 should only be listed)

2nd Report = “180 days to expiry” = with a condition Exp Date < (date of month selected + 180 days)

3rd Report = NPD SLOB = For 6 months from the month of selection going back 5 months ( selected month + 5 months prior), where Item code has a year of Launch of selected FY minus 1 and minus 2(ie, if the selected year is FY17, then only item codes which has FY17, FY16 and FY15 as launch year should be selected)

4th Report = Other SLOB = Same as NPD SLOB, but without condition of Launch year

Data Model Screenshot: Attached

Please help me in this regard.

Thanks,

Ashok

[ashok1203]

Hi All,

In this regard, i just need a suggestion on this. The user wants to select the monthyear field that consists in one table and based on the selection he need the Expired data. The problem i am facing here is, the both tables have an Key association. So, that the data when ever select the monthyear field it is automatically associating. Kindly help in this regard at earliest.

[adamdavi3s]

Have you investigated a master calendar?

[ashok1203]

No, We don't have a exact date field. To derive the master calendar. One table we are getting the Monthyear field from the source and other table has expire date. So, we didn't derive that.

[adamdavi3s]

I'm guessing your only way will be through set analysis, I would create a orphaned table for your month selection and then use this in your formula. Obviously you can also add fields then such as 'start of the month' which you can use.

I can't see your expiry date in your model so hard to advise exactly

[ashok1203]

The Expiry Date Column Name Should be LOT_Expiry_Date.