Expression variables (vDayRangeFilter) in QuickBooks Enterprise Advanced Reporting

vDayRangeFilter QuickBooks Advanced Reporting

The QuickBooks Advanced Reporting has a collection of convenient expression variables.

This blog discusses about one of them

vDayRangeFilter:

It is defined as

[Transactions.Txn Date]={“>=$(=Date(Max([Transactions.Txn Date])-$1))<=$(=Date(Max([Transactions.Txn Date])-$2))”},Year = ,Quarter = ,Month = ,Period  = ,Week    = ,Day = ,[Period ID] =

Where to use it:

This expression variable is used to show amount or quantity between two different number of days.

The following expression shows sales amount and quantity between 50th and 60th days.

sum({<$(vExprSales),$(vDayRangeFilter(60,50))>}[Transactions.Amount With Sign])

sum({<$(vExprSales),$(vDayRangeFilter(60,50))>}[Transactions.Quantity With Sign])

Few things to note here:

  • The number of days calculation is always done from the max transaction date of your company file and not from today’s date.
  • The large number always goes as the first parameter to this expression.

The vDayRangeFilter is the mother of all date range-based expression variables because it can be used for any date range like monthly, quarterly, YTD or anything else.

For example,

The following expressions are for last 60 days of the current and for the same period last year respectively:

sum({<$(vExprSales),$(vDayRangeFilter(60,0))>}[Transactions.Amount With Sign])

Just add 365 to parameter values to get the values for the last year:

sum({<$(vExprSales),$(vDayRangeFilter(455,365))>}[Transactions.Amount With Sign])

Let us know if you have any questions.

 

5 Comments

  1. Can I use this same variable to display only the last 3 transaction dates by item per customer?

      • If I use FirstSortedValue it still displays all dates. I only want it to display the last 3 dates.

        • try this in your dimension
          IF(aggr(rank([Transactions.Txn Date]),[Transactions.Item Full Name], [Transactions.Txn Date])<=3,[Transactions.Item Full Name])

          • Thank you. This is very close but it does not always seem to be displaying 3 transactions if that customer bought that Item more than 3 times. It works on some but not others.

Leave A Reply