Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Dpage
Contributor III
Contributor III

Date range in set analysis using date from a field

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.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.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 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 columnsWith If statement and no type and date columnsThe if statement appears to split the expression into individual transactions.The 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.

Labels (3)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

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.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

5 Replies
Dpage
Contributor III
Contributor III
Author

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.

 

Dpage
Contributor III
Contributor III
Author

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

Vegar
MVP
MVP

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.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Brett_Bleess
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Dpage
Contributor III
Contributor III
Author

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