How to slice comparing columns from different tables

Questions
,
#1

Hi guys,

I am pretty sure I did read something about my request somewhere but can’t find it anymore. My need is:

I have an Order table with a date column and a ref column refering to another table called Accounting periods.

In the table Accounting period I have a virtual column Yes/No type that determine if a row is the current accounting period or not. Formula to do so is : AND([date_début] <= TODAY(), TODAY() <= [Date_fin]).

First I need that the ref column in the order table compute automatically what is the correct Accounting period according to the date.

Then I would like to create a slice that filters all Orders fitting the current Accounting period.

Any advice on the way to do that ?

Thanks

#2

To determine whether the Order row is in the current accounting period, you might use a dereference expression from the Order row like:

[ref-to-acct-tbl].[in-period?]

where ref-to-acct-tbl is the name of the “ref column refering to another table called Accounting periods” and in-period? is the name of the “virtual column Yes/No type that determine if a row is the current accounting period or not”.

Use a slice row filter that uses the dereference expression above. It should be that simple.

See also:


#3

Hi Steve,

I think I got your point but your suggestion supposes that I choose manually the accounting period for the order. Then it would determine if the order is related with the current accounting period. That is how I use dereference usually.

What I need is that the column “Accounting period” in the “Order table” to be computed with an expression saying something like : look in the accounting period table and take the row that fit the condition : Order[Date] >= AccountingPeriod[startDate] , Order[Date] <= AccountingPeriod[EndDate]

1 Like
#4

It sounds like you want the slice to show all Order table entries for a chosen accounting period? How would the desired accounting period be indicated by the user?

#5

@Steve

I will manage the slice but yes here is the point. I do not want that the apropriate accounting period to be indicated by the user but calculated according to the order date.

For example, in accounting period table you have 2 rows :
Period 1 Start date 1st January 2020 End date 31st december 2020
Period 2 Start date 1st January 2021 End date 31st december 2021

Then in the Order table, if the order date is between the 1st january 2020 and the 31st december 2020, the column “Accounting period” would show “Period 1”. If the order date is between the 1st january 2021 and the 31st december 2021, the column “Accounting period” would show “Period 2”.

#6

Ah. Perhaps this as the App formula of a virtual column in the Order table?

FILTER(
  "Accounting Period",
  AND(
    ([_THISROW].[Order Date] >= [date_début]),
    ([_THISROW].[Order Date] <= [Date_fin])
  )
)
#7

Thanks Steve. Looks working but can’t dereference on this virtual column for other purpose while it’s a list type. I created another virtual column with an ANY expression while the list is supposed to show only one row. I’ll go further like this. Thanks again.

1 Like
#8

You could just wrap the original FILTER() expression itself in ANY() if you’d like to avoid the extra virtual column:

ANY(
  FILTER(
    "Accounting Period",
    AND(
      ([_THISROW].[Order Date] >= [date_début]),
      ([_THISROW].[Order Date] <= [Date_fin])
    )
  )
)
1 Like

Company

Platform

Pricing

Solutions

Support

Account

Built in Seattle, WA | Copyright 2019