Don't Quite Have A True Rollup - Need A Little Help, Please

[pnn44794]

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.

[sunny_talwar]

Your expressions looks good to me... it seems that the problem might be in the if condition for the flag, right? What exactly are you trying to do with the flag? or did I misread your situation?

[pnn44794]

Hello Sunny:

Thanks for the reply.  I think it might be a problem with the if condition, but I'm not positive.  I've attached two screenshots.  The first one is how it's working now and not what I need, and the second screenshot is what I'd like to have.

[pnn44794]

Ok. 

[sunny_talwar]

okay so this seems like a problem related to dimension... which of the columns here are dimensions? CIO / LOB, PPL#, Project Name, Orders In? Is there another dimension here? Something which might be in dimension but hidden from the presentation tab?

[pnn44794]

Hello Sunny:

Keen insight on the possible hidden column.    So the Dimensions in the straight table are:

CIO / LOB - This is an InLine load within the Load Script

PPL # - As described above, this is really DM #, but re-labeled for the straight table to PPL #

Project Name - As described above - This is really either WRM_Project_Name or WRM Title being re-labeled

Orders In - This is created in the Load script with If(Len(CREATE_DATE) > 0, 'Ordered', 'Not Ordered') as [ORDERS IN]

CREATED DATE - This is the hidden column you suspected (great intuition!) - This is created in the load script from CREAT_DATE

The remaining columns are expressions as listed in my original post above.

[sunny_talwar]

My guess is that if you remove CREATE_DATE from dimension, it will give you a single row.... if this is true, then the problem is that since CREATE_DATE is used as one of the dimensions, each of the expression are evaluated based on not just the other dimensions, but CREATE_DATE as well... for instance we see three rows for 23389 which might be associated with 3 CREATE_DATEs

[pnn44794]

Hello Sunny:

You are right.  If I remove CREATED DATE, then I get just one row for PPL # with the correct Outstanding Units.  The problem is and it's because of the way I'm doing it, that then breaks color highlighting I'm doing.

For the first 7 columns, I highlight them yellow if Order Date is < 8 days old.  I was using Background color for the dimensions and Expressions with =If(Today(1) - [CREATED DATE] < 8, Yellow()).  Do you have another way to highlight the first seven columns by row?

[sunny_talwar]

May be using Min or Max

=If(Today(1) - Min([CREATED DATE]) < 8, Yellow())

or

=If(Today(1) - Max([CREATED DATE]) < 8, Yellow())

[pnn44794]

As an Expression or Calculated Dimension?  I'm guessing Calculated Dimension.