AP Trended Spend by Vendor

Hi Brad,

The error message you got trying to query the Vendor table - is the DEFAULT_LEAD_TIME field a custom field? I’d like to troubleshoot this on our end and fix it.

Since your vendor IDs are prefixed by the entity, you could simply modify the cell that has =SI.EXPANDVENDORRANGE to add a filter, ex: E101-??????? - the first portion will be taken from the location field.

I’m not in front of my computer but can show you the exact formula when I’m back at my desk.

P.S. I emailed you regarding your other question on fixed asset reporting 

Gabriel,

The suggest query returns a N/A along with the "formula to review" message of "XL03000006 The provided field is not supported for querying entity vendor => DEFAULT_LEAD_TIME [Support ID: 24g-VWEB035%7EZseBPP490UO4hLx-FnWWXwAAAAI] "

As for entity-specific vendors, we have been using a vendor number prefix.  i.e. xxx-nnnnnnn where xxx = alpha-numeric characters specific to an entity.

On my first attempt at this report, I went with the entire APBill table filtering only on the MEGAENTITYID. =SI.QUERY("Sage","APBILL","MEGAENTITYID='E114'")

Brad,

Thanks for your feedback. The =SI.EXPANDVENDORRANGE() just gives you all the vendors; I assumed you would have vendors that are used by multiple entities. In my environment, none of my vendors are entity private. 

If you put this into a cell: =SI.QUERY("Sage","VENDOR",,"VENDORID,NAME,MEGAENTITYID") -- does it show you any values in the "MEGAENTITYID" column? Or do you have another way to tie a vendor to a specific entity?

The auto-hide zero rows feature will hide vendors with no activity for the selected location and date range; potentially we could eventually another formula that ensures we ONLY display vendors with activity as described in my video, but we don't have such a function yet.

Gabriel,

This looks great!  The 'From AP' tab works great.  I added the Description as a field just to enhance details for our managers.  I think I am still getting too much data on the 'From GL' tab, however.  Even with the filter set to a specific location, it returns vendors across all entities.  Can I apply an entityID/locationID filter to the SI.EXPANDVENDORRANGE function?  Would this be considered a range?

Hi Brad,

I created two versions -- one that works at the GL level, and another one that works at the AP level. There are differences between both which I explain in this video: https://www.loom.com/share/2f42acbe714041fb9b059167f6d08846

The file is attached to this message.

Regarding parent-child, I didn't do anything special for now. Do you have parent-child relationships set-up between vendors? If that's the case then this can be controlled by setting the IncludeChildDimensionBalances argument of the =SI.TURNOVER() function to TRUE or to FALSE depending on your needs. This will work in the GL version of the AP Trended Spend report.

This is documented here: https://helpintacct.velixo.com/support/solutions/articles/153000014555-si-turnover#Example-5---Include-Child-Dimension-Balances-or-not

Related question: when including child balances in the balances, will you still want to display child accounts? I can show you how to filter them out.

Screenshot of the report:

Gabriel,

I appreciate your efforts!  The parent/child child option by default should be both parent and child.  Once we mature in our use/understanding of the tool, we will start to offer reporting at the child level.

Currently we have about 450 vendors across 25 entities.  Some of the entities have data for 10+ years and others have only 2 years.  From a practical standpoint, there are going to be tens of thousands of invoices.  I was thinking if I could filter it with a 12-month view, I could somehow throttle the data.

The end goal is to provide a list of vendors, their monthly vendor spending for each entity.  This would the respective GM's a starting point to evaluate how they are spending as well as to see if reduction initiatives are working.

I can also see the benefit to having this by GL account so that might be attractive to have in a back pocket.

Thanks again!

Brad,

I'll build a sample for you. I have a few questions:

- What's the Parent/Child option?

- How large is the list of vendors and total number of transactions that will be retrieved? The reason I'm asking is because we can build this two ways:

#1 by querying the AP module to get total by vendor

#2 by doing it at the GL level

I am trying to create a similar report and would like to know if someone has some guidance on how they may have created something like this image:

Thanks,

Brad

Thanks Harry/Damien.

Matt, if you have a particular layout in mind, can you please share it here? We can create a working sample that you'd be able to use.

Hi Matt

You could use our GL function indeed as mentioned by Harry, or create a SI.QUERY on object: APBILL with the relevant columns including an aggregated amount column (e.g. SUM(TotalEntered) ), then using an Excel Pivot table to plot the data by month.

I would think that, if you generate a list of expense account groups in which you are interested and then apply the Location and Vendor dimensions to the SI.TURNOVER function for each month, that should give you that information.

e.g.,