Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

I use QuickBooks Advanced Reporting based on Qlik.

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??

5 Replies
ychaitanya
Creator III
Creator III

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)

Anonymous
Not applicable
Author

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

ychaitanya
Creator III
Creator III

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

Thank you.

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