Hello:
I have a straight table where I'm trying to roll up orders into a single row per DM # based on several criteria and it's not quite working. I need for all DM #'s that meet the following criteria:
If((PROD_KEY3 <> 'VSI-D Clustered' and Len(Trim(PROD_KEY3))>0) and [STATUS_ORIGINAL] <> 'Provisioning' and REQUEST_TYPE Like 'New*' and NET_ASSETS > 0 and Len(Trim(COMPLETED))=0, 1, 0) as Flag <--This is located and executed within the load script
I have the following expressions:
Sum({<Flag = {1}>}[NET_ASSETS]) <--Labeled as Outstanding Units
Date(Min({<Flag = {1}>}[CREATED DATE])) <--I need the oldest CREATED DATE for each particular DM # that meets the If statement above
Date(Max({<Flag = {1}>}[Promise Date])) <--I need the most recent Promise Date for the DM # with the oldest date
Date(Max({<Flag = {1}>}[Trend Date])) <--I need the most recent Trend Date for the DM # with the oldest date
So, if I have DM # 12345 with three different Order ID's with quantities of 5, 10 and 15 respectively, CREATED DATES of 01/05/2018, 02/01/2018 and 03/05/2018, respectively, Promise Dates of 01/20/2018, 02/15/2018 and 03/25/2018, respectively and lastly, Trend Dates of 02/10/2018, 03/25/2018 and 05/01/2018, respectively, I need to return one row with a total Outstanding Units of 30, an Order Date (CREATED DATE) of 01/05/2018 (the oldest date), a Promise Date of 03/25/2018 (most recent) and a Trend Date of 05/01/2018 (most recent) after meeting the above If condition.
Does this make sense?
As always, thanks in advance for any and all responses and help.
Ok. Will try now.
