topic Re: I use QuickBooks Advanced Reporting based on Qlik. in QlikView

I use QuickBooks Advanced Reporting based on Qlik.

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I use QuickBooks Advanced Reporting based on Qlik.

In one of my reports a diminsion is coded as follows:

= if(([Transactions.Inventory Table] = 0)
and (Transactions.Pending = 0 or IsNull(Transactions.Pending))
and Match([Transactions.Txn Type], 'Invoice', 'Credit Memo')
and [Transactions.Amount] <> 0 and IsNull(Transactions.Amount)=0

and (([Transactions.Txn Date] >= MinDate and [Transactions.Txn Date] <= MaxDate)
or ([Transactions.Txn Date] >= MinDate2 and [Transactions.Txn Date] <= MaxDate2))

, [Transactions.Line Items Txn Type])

//Variables:
//   Mindate set to 01/01/2017
//   Maxdate set to 03/31/2017
//   Mindate2 set to 01/01/2018
//   Mindate2 set to 03/31/2018

The result is that I get al years and all transactions. It obviously does not like my "and/or" statement. All other conditions noted here have been tested and have the desired results. Can anyone help??

Re: I use QuickBooks Advanced Reporting based on Qlik.

ychaitanya — Thu, 22 Mar 2018 17:17:22 GMT

Filter for [Transactions.Txn Date] is basically written if it falls on the First Quarter of the year ??

rather you use as

match(Quarterstart([Transactions.Txn Date]),MinDate,MinDate2)

Re: I use QuickBooks Advanced Reporting based on Qlik.

Anonymous — Thu, 22 Mar 2018 18:15:57 GMT

Thank you so much for your thoughtful reply. If the variables (this is the real need) would be:

//Variables:

// Mindate set to 01/15/2017

// Maxdate set to 02/15/2017

// Mindate2 set to 01/15/2018

// Mindate2 set to 02/15/2018

Then it’s not a quarterly search nor a monthly search nor annual, it’s a one random date range and a second random date range search.

Thank you for your suggestion but I’m sure the problem is in the ‘ and/or’ routine as in my original coding example.

Dan

Re: I use QuickBooks Advanced Reporting based on Qlik.

ychaitanya — Thu, 22 Mar 2018 18:29:15 GMT

Conditions looks good semantically, can you use the fields in the list boxes and select the filters passed above in the IF Conditions and see what the results are which helps you to consider the logic

Re: I use QuickBooks Advanced Reporting based on Qlik.

Anonymous — Thu, 22 Mar 2018 18:39:21 GMT

I did as you suggested with the same result, all sales transactions were included no matter the date. I’ve been doing ifs, ands, ors and butts for many years in a number of languages and the semantics look correct. I’m baffled.

Thank you for your interest and help.

Dan

Re: I use QuickBooks Advanced Reporting based on Qlik.

Anonymous — Tue, 10 Apr 2018 16:57:50 GMT

Thank you.

I spent too much time on this possible request, I'm quitting.