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.?
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.
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
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.?
Attachments (2)
Screenshot 2....png
43.1 KB
Screenshot 2....png
148 KB
0 Votes
Gabriel Michaud posted 3 months 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
Nathan Gayeski posted 3 months ago
Thank you both! I will test that out now.
0 Votes
Markens Juste posted 3 months 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 posted 3 months 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