Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Set Analysis Date Range

Hi Qlikview Community,

I really hope that you could assist me with the following.

I'm having some trouble creating a set analysis that will calculate the amount paid during a certain period of time per client. I have 2 date fields which I imported from an Excel file. The two date fields (per client) are as follow:

* DateField_1 = Start date of membership

* DateField_2 = End date of membership

I require the set analysis to calculate the sum total of amounts paid between the two mentioned date. A normal Excel if statement should look something like this:

if(DateField_1 <= DateField_2, sum(AMT_Paid), 0)

Please help! I've read many posts, but am a bit confused with regards to the use of the " " and ' '. Also not sure when to use it.

Cheers,

Niel

1 Solution

Accepted Solutions
Employee

Hi Daniel,

Check this document and the attached QlikView app that solves that issue using set analysis.

Basically, the expression is:

`Sum({< MasterDate = {">=\$(=Date(Start_Of_Membership)<=\$(=Date(End_Of_Membership)"} >} AMT_paid)`

Hope that helps.

Miguel

5 Replies
Employee

Hi Daniel,

Check this document and the attached QlikView app that solves that issue using set analysis.

Basically, the expression is:

`Sum({< MasterDate = {">=\$(=Date(Start_Of_Membership)<=\$(=Date(End_Of_Membership)"} >} AMT_paid)`

Hope that helps.

Miguel

Master II

Not applicable
Author

Many thanks Miguel and Alan!

Not applicable
Author

Question about the topic of date ranges:

What if you wanted a different Start_Of_Membership and End_Of_Membership for each client?

For example:

Client          Start_Of-Membership          End_Of_Membership

A                    1/1/2013                              31/12/2013

B                    1/3/2013                              31/8/2013

C                    1/6/2013                              31/12/2013

Would this method still work?

Partner - Contributor III

Thanks Miguel,

Always trouble with the correct syntax.

Now it works for me

Jan

Community Browser