Set Analysis Date Range

[#1]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[rustyfishbones]

Try this video I made on YouTube http://youtu.be/afHN_WYyHqg

[#4]

Many thanks Miguel and Alan!

Really appreciate your help!

[#5]

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?

[janderooij]

Thanks Miguel, 

Always trouble with the correct syntax. 

Now it works for me

Jan