12 Replies Latest reply on Jun 18, 2019 6:05 AM by muhammad usman

# Calculated Field (LOD and Aggregation)

I have an excel file which have three columns

Transaction Date

Ending Inventory (available inventory)

Product Name

Is there any way i can find the sum of Ending Inventory for All Products By their  last Transaction Date ? I need to calculate this value

• ###### 1. Re: Calculated Field (LOD and Aggregation)

It can be done through LOD. But are you looking to get sum of the measure at date level? if so, it can be written as

{Fixed TransactionDate: SUM([Measure])}

If you just want the grand total, {Fixed: SUM([Measure])}

• ###### 2. Re: Calculated Field (LOD and Aggregation)

Hi,

Try this calc

{ Fixed [Product name]: Sum(Ending inv...)} or simply { Fixed : Sum(Ending inv...)}

Manoj

• ###### 3. Re: Calculated Field (LOD and Aggregation)

Yeah at the Date Level where It should get each product latest transaction date and their ending inventory and combine them

• ###### 4. Re: Calculated Field (LOD and Aggregation)

Hi Usman,

Try out this formula:

{ FIXED [Product Category]:

sum((IF [Order Date]={ FIXED [Product Category]:MAX([Order Date])} THEN [Sales] END))}

Replace Product Category with product# and Order Date with Transaction date and Sales with Inventory.

This should give you the desired result.

Let me know if this helps you

Regards,

Tanusri

1 of 1 people found this helpful
• ###### 5. Re: Calculated Field (LOD and Aggregation)

It should get each product latest transaction date and their ending inventory and combine them.

• ###### 6. Re: Calculated Field (LOD and Aggregation)

For each product latest transaction date, write a LOD as

{Fixed Product: MAX(Transaction Date)}, drag this into rows. For the measure, I don't think you need to use LOD, just drag the measure on to the text marks.

• ###### 7. Re: Calculated Field (LOD and Aggregation)

Hi,

Try this, it'll give your grand total.

{FIXED: SUM(IIF({FIXED [Product Name]:MAX([Transaction Date])}=[Transaction Date],[Ending Inventory],NULL))}

Let us know if this work.

Mahfooj

1 of 1 people found this helpful
• ###### 8. Re: Calculated Field (LOD and Aggregation)

This Helps Thank You
Have a Good Day

• ###### 9. Re: Calculated Field (LOD and Aggregation)

Have a good day

• ###### 10. Re: Calculated Field (LOD and Aggregation)

This also Works
Thank you

• ###### 11. Re: Calculated Field (LOD and Aggregation)

Hi Usman,

As per your requirement, based on the table shown then to get the total value which is 45,744,156 use the following code :

{ Fixed : sum( [ End Investment ] ) }

Another approach will be,

Step 1:  Create Product Name wise Max Transaction Date

Field Name : Max_Date

Code :

{ Fixed [ Product Name ] : MAX ( [ TransactionDate ] ) }

Step 2 : Find the End_Investment value for that Max transaction date

Field Name : Max_Date_EndInvest

Code :

IF  [TransactionDate] =  [ Max_Date] then [ End_Investment ] End

Step 3 : Generate productname and max transaction wise End_Investment

Field Name : ProductName_Invest

{ Fixed [ Product Name ]  : SUM( Max_Date_EndInvest ) }

Step 4 : To find total End_Investment

{ Fixed : sum ( [ ProductName_Invest ] ) }

Here you will be able to generate total End_investment for the product.

Thanks,

Anugraha

• ###### 12. Re: Calculated Field (LOD and Aggregation)

Thanks you