Skip to main content

Morning all,

 

I need a hand making this calculation and I am open to ideas.  I am using 2018.1

 

Here is my issue:  Do to some bad design and logic I have parent and child records in my table but I have no other column that will help me to "filter" out the child records.  The result of this is values that are doubling but it only happens in one area.

 

This is what my underlying table looks like:

 

Transaction TypeTravel Order NumberTransaction DateAmountTravelOrder 11/1/2019100TravelOrder 12/1/201975TravelOrder 12/1/201925TravelOrder 23/1/2019500TravelOrder 24/5/2019125TravelOrder 24/10/201975TravelOrder 24/15/2019300TravelOrder 35/1/2019700TravelOrder 36/1/2019275TravelOrder 36/10/201935TravelOrder 36/20/2019390TravelOrder 47/1/20191000

 

As you can see each transaction has a type.  Types could be Travel, Supplies, Overtime, etc.  In my case, the issue is with Travel.

 

Now I have highlighted the same table to break out the parent record from the child record:

 

This is the logic that I need and cannot seem to come up with:

 

Condition 1: For Transaction Type = Travel Records

Condition 2: Where the Travel Order Number >1

Then return the Amount equal to the MIN(Transaction Date)

 

If all those conditions are met then this is what I should see:

 

 

Now what I have been trying to do is this:

 

Attempt 1:

 

IF ATTR([Transaction Type])='Travel' AND COUNT([Travel Order Number])>1 AND ATTR([Transaction Date])=MIN([Transaction Date]) THEN SUM([Amount])

ELSE SUM([Amount])  //This is for records that are not travel.

END

 

Attempt 2:

 

{FIXED [Travel Order Number],[Transaction Date]:SUM(IF COUNT([Travel Order Number]>1 AND [Transaction Date]=MIN([Transaction Date]) THEN [Amount])}

 

I think the LOD approach might be best but I cannot figure out how to get the IF THEN logic inside the LOD Expression.

 

Thank you,

 

Scott

5 risposte
  1. 28 ago 2019, 15:40

    Hi Scott,

     

    LODs are a good approach but there are a few things wrong with your syntax. The correct LOD for your logic requires a nested LOD calculation, e.g.

     

    { FIXED [Number] : SUM(IF [Date]= { FIXED [Number] : MIN([Date])} THEN [Amount] END) }

     

    I don't understand your number>1 logic but COUNT is definitely the wrong function to use there.

     

    A simpler LOD that works with your data since the parent is the sum of the children is:

     

    { FIXED [Number] : MAX([Amount]) }

     

    And if you don't include date in your view, you can skip the LOD, and for each order just show

     

    MAX([Amount])

     

    Regards,

    Zach

0/9000