topic Re: Date range in set analysis using date from a field in QlikView

Date range in set analysis using date from a field

Dpage — Sat, 16 Nov 2024 19:51:36 GMT

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).With set analysis of invoices the number is correct for the total qty. but I need to limit it to sales (qty. invoiced) after the job start date.This set analysis gives me the correct qty. invoiced in total.With the If statement which does work but only if I add a transaction type column and a transaction date column and the qty. is not aggregated.With If statement and no type and date columnsThe if statement appears to split the expression into individual transactions.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.

Re: Date range in set analysis using date from a field

Dpage — Fri, 18 Oct 2019 23:44:27 GMT

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.

Re: Date range in set analysis using date from a field

Dpage — Fri, 18 Oct 2019 23:49:52 GMT

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])

Re: Date range in set analysis using date from a field

Vegar — Sat, 19 Oct 2019 06:48:21 GMT

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.

Re: Date range in set analysis using date from a field

Brett_Bleess — Wed, 23 Oct 2019 20:59:35 GMT

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

Re: Date range in set analysis using date from a field

Dpage — Wed, 13 Nov 2019 21:34:19 GMT

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