topic Re: Set Analysis - Date Ranges in QlikView

Set Analysis - Date Ranges

NielBerg — Sat, 16 Nov 2024 01:51:52 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 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

Re: Set Analysis - Date Ranges

tresB — Mon, 25 Nov 2019 07:43:33 GMT

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

Re: Set Analysis - Date Ranges

Taoufiq_Zarra — Mon, 25 Nov 2019 08:15:38 GMT

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

Re: Set Analysis - Date Ranges

marcus_sommer — Mon, 25 Nov 2019 10:20:00 GMT

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

Re: Set Analysis - Date Ranges

Brett_Bleess — Mon, 09 Dec 2019 18:28:43 GMT

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