Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
*Note: I am working in Quick Books Advanced Reporting which uses Qlikview software to build the reports. I've been able to solve most of my issues using info. I have found in the Qlik community but I'm sure there are distinctions associated with QBAR.
I am using set analysis to calculate the number of items invoiced after a particular start date. The start date comes from a field called Customer.Job Start Date. I would like to make my set analysis only calculate the qty. invoiced after the Customer.Job Start Date. I can do this using an if statement but this seems to then calculate the expression as individual transactions instead of aggregating the data. (See pictures below).How do I get the if statement into my set analysis? I can't seem to make it look at transaction dates > than the customer job start date.
I little different approach might work. See below.
Sum({<[Transactions.Txn Type]={'Invoice'}, TransactionsID = {"=[Transactions.Txn Date]>=[Customer.Job Start Date]"}>}[Transactions.Quantity])
If not, then post a picture (or describe) of your data model showing all the fields mentioned by you above.
What I'm seeing in most posts regarding how to use greater than in set analysis is something like this
Sum({<[Transactions.Txn Type]={'Invoice'}, [Transactions.Txn Date]={">=[Customer.Job Start Date]"}>}[Transactions.Quantity])
I'm not sure if it's the field that is the problem. Most examples use either specific dates or some form of Date function. I just want it to look at the date value in Customer.Job Start Date and calculate quantities invoiced after that date.
Any advice would be greatly appreciated.
This slight change is giving me a result at least but now appears to be looking at all invoices again rather than after the start date.
Sum({<[Transactions.Txn Type]={'Invoice'}, [Transactions.Txn Date]={">=$([Customer.Job Start Date])"}>}[Transactions.Quantity])
I little different approach might work. See below.
Sum({<[Transactions.Txn Type]={'Invoice'}, TransactionsID = {"=[Transactions.Txn Date]>=[Customer.Job Start Date]"}>}[Transactions.Quantity])
If not, then post a picture (or describe) of your data model showing all the fields mentioned by you above.
Best I have in addition to what Vegar posted is a Design Blog post that may be of some use:
https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511
Regards,
Brett
Thank you for the response. I apologize as I have been out of the office for the last few weeks.
Your solution worked though I had to use Transactions.Txn ID. I figured it out once I thought about it. Thank you for your help.
David