GL Account Ranges within SI.QUERYFILTER

Posted about 1 month ago by Nathan Gayeski

N
Nathan Gayeski
Answered

0 Votes

Hello, I would like to know if we can multiple ranges within a SI.QUERYFILTER? I understand we can have a range of 2000 to 3999. However, I would like to have a range of 2000 to 2500 and also a range of 3000 to 3200. Is that possible?


Alternatively, could we have a selection of accounts? Example 3001, 3002, 3110, 3200, etc.?

0 Votes

Gabriel Michaud

Gabriel Michaud posted about 1 month ago Admin Best Answer

Hi Nathan,

The trick is to use SI.EXPANDACCOUNTRANGE() to transform your account range into a format that SI.QUERYFILTER can understand:

=SI.QUERYFILTER("Sage","GLDETAIL","ACCOUNTNO",SI.EXPANDACCOUNTRANGE("Sage","2000:2500;3000:3200"))

(note: you will obviously not want to hardcode the accounts in the formula, but reference a cell!)

If you put SI.EXPANDACCOUNTRANGE() in its own cell you'll see that it returns multiple cells - it's what Excel calls a "dynamic array function". The range syntax you can use is the same as all our other financial functions -- see here for reference: https://help.velixo.com/support/solutions/articles/153000012270-filtering-techniques-for-use-with-many-velixo-functions

Note that this should be used for a reasonably-sized range; the query could become too long otherwise. If you need just a from/to range the technique you used with >= and <= will work better.


0 Votes


3 Comments

Sorted by
N

Nathan Gayeski posted about 1 month ago

Thank you both! I will test that out now.

0 Votes

Markens Juste

Markens Juste posted about 1 month ago Admin

Hi Nathan, 

Thank you for contacting Velixo Support.

Yes, it is possible. We have this article that shows many possible combinations:  Filtering techniques for use with many Velixo functions : Velixo for Sage Intacct Help 


When you mention if it is possible to have a selection of accounts, are you referring to a drop-down list?


0 Votes

Gabriel Michaud

Gabriel Michaud posted about 1 month ago Admin Answer

Hi Nathan,

The trick is to use SI.EXPANDACCOUNTRANGE() to transform your account range into a format that SI.QUERYFILTER can understand:

=SI.QUERYFILTER("Sage","GLDETAIL","ACCOUNTNO",SI.EXPANDACCOUNTRANGE("Sage","2000:2500;3000:3200"))

(note: you will obviously not want to hardcode the accounts in the formula, but reference a cell!)

If you put SI.EXPANDACCOUNTRANGE() in its own cell you'll see that it returns multiple cells - it's what Excel calls a "dynamic array function". The range syntax you can use is the same as all our other financial functions -- see here for reference: https://help.velixo.com/support/solutions/articles/153000012270-filtering-techniques-for-use-with-many-velixo-functions

Note that this should be used for a reasonably-sized range; the query could become too long otherwise. If you need just a from/to range the technique you used with >= and <= will work better.


0 Votes

Login or Sign up to post a comment