topic Re: Set Analysis Date Range in QlikView

Set Analysis Date Range

Sat, 18 May 2013 12:58:23 GMT

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

Re: Set Analysis Date Range

Miguel_Angel_Baeyens — Sat, 18 May 2013 13:31:36 GMT

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

Re: Set Analysis Date Range

rustyfishbones — Sat, 18 May 2013 16:26:07 GMT

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

Re: Set Analysis Date Range

Sun, 19 May 2013 08:40:10 GMT

Many thanks Miguel and Alan!

Really appreciate your help!

Re: Set Analysis Date Range

Wed, 22 Jan 2014 16:15:04 GMT

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?

Re: Set Analysis Date Range

janderooij — Fri, 29 Nov 2019 15:01:07 GMT

Thanks Miguel,

Always trouble with the correct syntax.

Now it works for me

Jan