0

Creating Rows using a calculated field, and filtered by another table

Karen Atkinson

September 18, 2019 18:46

None

Follow

I am trying to create a report of the Top 10 Vendors / Item Category ... listing the past 3 years of purchases.

The only thing not working, is the sorting of the Vendors ... It is listing some Vendors with all 0 costs across all 3 date ranges, and in some cases, it is listing vendors that don't purchase in that category ...

I would like to use the Current YTD Date Filter, and the Item Category filter from column B ... 

This is what I have right now ...

=NL("Rows","Vendor",,"-Purchases ($)","<>0","Date Filter",Options!$C$9,"Limit=","10","LINK=","Value Entry","Source No.","=No.","Item Cat Level 2",$B14)

It is sorting based on "Purchases", but I don't know how to sort it based on the calculated value from col "Curr YTD Cost"

Curr YTD Cost  =NL("Sum","Value Entry","Cost Amount (Actual)","Posting Date",Options!$C$9,"Item Ledger Entry Type","Purchase","Item

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Bryan Robinson September 19, 2019 16:36

  Hi Karen, best to review this article https://support.jetglobal.com/hc/en-us/articles/115006132828-Sort-by-Sum-or-count-with-Dynamics-NAV

  The difference is that you will need to have the NF created in the SUM function for each previous level of your report (i.e. Item Category and Market Type)  It's a little more advanced than just a standard Sort by Sum.

• 0
  
  Karen Atkinson September 23, 2019 13:16

  If I do this, it says that it brings back the numbers as text, and I won't be able to add them together, and then by default calculate the "other" row ... (Other is total value for the Item Category - sum[top 10 vendors] )

  If there a way that I can use the Calculated column "Curr YTD Cost" (as this is working), as the sort criteria in the formula to replace the "-Purchases ($)" ?

  =NL("Rows","Vendor",,"-Purchases ($)","<>0","Date Filter",Options!$C$9,"Limit=","10","LINK=","Value Entry","Source No.","=No.","Item Cat Level 2",$B14)

  But it needs to bring back the actual value, not a text value.

• 0
  
  Bryan Robinson September 23, 2019 13:26

  You can only sort on a Filter within a replicator.  You can't tell a ROWs function to look at a different cell with a standard NL SUM to make the sort happen.

  As previously mentioned, this is a bit more advanced than a standard sort by sum function and probably would require working with a consultant to ensure everything is working and so the consultant can see your entire report.  It will be important to be able to see all the functions in the report and how you built it to understand how the sort on the NL Sum you have built will work.

• 0
  
  Karen Atkinson September 23, 2019 13:27

  OK - Thanks for your assistance ;-)