Set Analysis - Date Ranges

[NielBerg]

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 or ETL script 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)

I have received assistance from the community and I'm currently using the following script:

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

The problem is that it only works when I make a selection. I am also happy to use it in the load script. Just need it to calculate without having to select anything. There is a common denominator (primary key) between the 2 sheets in the form of a client ID. 

Cheers,

Niel

[tresesco]

Could you share your app with sample data explaining the expected output format?

[Taoufiq_Zarra]

Hi Niel,

you need selection because there are several possibilities in the dates.
so if you want to use it in a graph you must specify a characteristic that allows Set Analysis to retrieve a date, for example, Max(date)...
otherwise, you can use it in the script, and I think that's what you want, I suggest you use the pick() function in this case (here's how to use it Peek)

Cheers,

Taoufiq

[marcus_sommer]

It couldn't be solved with a classical set analysis because they worked like a selection - means the set condition is applied globally and not on your dimensional level.

You could try it in this way:

Sum({< MasterDate = {"=MasterDate>=Start_Of_Membership and MasterDate<=End_Of_Membership"} >} AMT_paid)

which is quite the same like:

if(MasterDate>=Start_Of_Membership and MasterDate<=End_Of_Membership, Sum(AMT_paid))

Quite common for such a use-case is to resolve the start/end-date to a real date and to connect it with the date from the master-calendar. For this take a look here: IntervalMatch 

- Marcus

[Brett_Bleess]

Niel, did any of the posts help you with a final solution?  If so, please be sure to return to the post and use the Accept as Solution button on the post(s) that helped you get a working solution.  If you did something different, consider posting that and then mark it.  Marking things gives the posters credit for the assistance and lets other Community Members know what actually worked.  

The only additional thing I have is a Design Blog post that may be of some use to you:

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

Sorry I have nothing better for you.

Regards,
Brett