topic Re: Date range Set Analysis question in QlikView

Date range Set Analysis question

alexis — Fri, 04 Apr 2014 15:19:33 GMT

Consider the diagram below:

The Calendar is linked with Package via PackageDate
Package is linked to Activity via PackageID

The users selects a period on the calendar (say a Year and a Month)

I have defined 2 variables:

vMinPackageDate = Min(PackageDate)

vMaxPackageDate = Max(PackageDate)

These correctly store the limits of the range selected

My requirement is to SUM(ActivityAmount) for the date range defined by vMinPackageDate and vMaxPackageDate

= sum({$<ActivityDate={">=$(Date(vMinPackageDate))<=$(Date(vMaxPackageDate))"}>} ActivityAmount)

I am getting results that are NEARLY right; the problem is that when the user selects the period, this affects the range of Package records which in turn affects the range of selected records in Activity.

I want the SUM(ActivityAmount) for the date range vMinPackageDate ... vMaxPackageDate independent of Package...

I have tried various variants like:

= sum({$<PackageDate=, ActivityDate={">=$(Date(vMinPackageDate))<=$(Date(vMaxPackageDate))"}>} ActivityAmount)

but nothing seems to work. Any pointers?

Thanks in advance

Alexis

Re: Date range Set Analysis question

MK_QSL — Fri, 04 Apr 2014 15:34:29 GMT

Your sample data would be highly preferable to give you answer.

Re: Date range Set Analysis question

Gysbert_Wassenaar — Fri, 04 Apr 2014 15:35:51 GMT

Try defining your variables as =Min({1}PackageDate) and =Max({1}PackageDate)

Re: Date range Set Analysis question

alexis — Fri, 04 Apr 2014 15:41:38 GMT

Hi Gysbert

Thanks for replying.

If I define my variables like that they will always contain the same values irrespective of user selection.

I want the user to use the calendar to select a data range and that's it! Using that date range I then want to select my Activity records independently of the Package records above them if that makes sense!

Alexis

Re: Date range Set Analysis question

Fri, 04 Apr 2014 15:42:34 GMT

To not consider the Package Selections try:

= sum({$<Package=, ActivityDate={">=$(Date(vMinPackageDate))<=$(Date(vMaxPackageDate))"}>} ActivityAmount)

Sample data and desired result would be very helpful.

Re: Date range Set Analysis question

alexis — Fri, 04 Apr 2014 15:44:26 GMT

Thanks Manish - what you see here is a simplistic view of a fairly complex application so to create data would be quite a task (I think 🙂 )

Re: Date range Set Analysis question

alexis — Fri, 04 Apr 2014 15:47:43 GMT

Hi Patrick

"Package" is a table name not a field so not sure how "Package=," will work.

Package table IS joined to the Activity table and that is the very reason that my results are not what I want.

I basically want to return the records that are defined by the date range in the calendar (as stated before) independent of Package...

Re: Date range Set Analysis question

alexis — Fri, 04 Apr 2014 16:01:01 GMT

You would have thought that:

= sum({1<ActivityDate={">=$(Date(vMinPackageDate))<=$(Date(vMaxPackageDate))"}>} ActivityAmount)

(i.e. replacing the "$" with a "1" to consider the entire set but this one always returns that entire record set ignoring my date range selection...

Re: Date range Set Analysis question

Gysbert_Wassenaar — Fri, 04 Apr 2014 16:11:16 GMT

I'm not following you. You want to select a date range in the PackageDate field. That selection will determine the min and max values in your variables..... which are values you say are the wrong values since they relate to a limited set of Packages (the possible values resulting from your selecting in PackageDate). I don't understand why or what you feel the correct values should be.

Perhaps you want to create an additional calendar for the ActivityDate and select the date range in that field.

Re: Date range Set Analysis question

Fri, 04 Apr 2014 16:34:12 GMT

Looks like your easiest way would be to create a Summary table. (No need for variables!) Kindly see attached sample.

or a simpler Summarytable (depends on the your granularity) would be-

SummaryTable:

load

ActivityDate as Calendar_Date,

sum(ActivityAmount) as _ActivityAmount,

'ActivityAmount' as SummaryType

resident Activity

group by ActivityDate

;

You can use this Summary Table for other Summaries, just create a different 'SummaryType'. HTH!

Re: Date range Set Analysis question

alexis — Fri, 04 Apr 2014 16:36:14 GMT

Hi Gysbert

Certainly creating an additional calendar would definitely solve the problem but I am avoiding it.

As to what I am trying to do I hope the following illustration helps!

Re: Date range Set Analysis question

alexis — Fri, 04 Apr 2014 17:02:30 GMT

Thanks Patrick - that was a very elegant solution and I will bear it in mind.

Because of the complexity of the "real" application, it looks like it warrants its own dedicated calendar (I think!)

What "theme" did you use in the example application - it looks very clean.

For now I have awarded you "helpful" but will award "Correct" if I end up using your approach (which I must say is growing on me) !

Thanks again

Re: Date range Set Analysis question

Fri, 04 Apr 2014 18:06:11 GMT

Thanks Alexis. This approach actually creates a better data model that utilizes the functionalities of Qlikview. Using multiple Calendars makes you end up with something like a Snowflake schema and ultimately more stuff to maintain.

Not a different theme though, just have Styling mode set to Advanced. 😃

Re: Date range Set Analysis question

Gysbert_Wassenaar — Fri, 04 Apr 2014 18:08:36 GMT

Right, I understand the result you're looking for.

Certainly creating an additional calendar would definitely solve the problem but I am avoiding it.

Why? It is the obvious solution imho. See this blog post: Why You sometimes should Load a Master Table several times. And perhaps this discussion has some useful bits too: Tutorial - Using Common Date Dimensions and Shared Calendars

You could also try using variables and calendar objects without actually making selections in the date fields.

Re: Date range Set Analysis question

alexis — Fri, 04 Apr 2014 19:12:52 GMT

Hi Gysbert

I went for a separate calendar in the end and it works fine - kind of 🙂

When I select a particular year (say) from the new calendar it comes back with all the correct numbers as I expect it - let's suppose though, that for the selected year I wish to display the data in a chart (by month) how do I achieve the "point in time" for each month?

Any ideas?

Alexis

Re: Date range Set Analysis question

Gysbert_Wassenaar — Fri, 04 Apr 2014 19:33:51 GMT

Can you explain what you mean by "point in time" for each month.

Re: Re: Date range Set Analysis question

alexis — Fri, 04 Apr 2014 20:18:46 GMT

Hi Gysbert,

The attached application example should explain what I mean

Thanx

Alexis

Re: Date range Set Analysis question

Gysbert_Wassenaar — Sat, 05 Apr 2014 14:51:07 GMT

Ok, I think I understand the problem. Set analysis creates one set per chart. You want a set per dimension value, for each month for example. Technically that's possible with a pick-match combination, but using an AsOf table solution is a lot easier. See this document for more information about AsOf tables: Calculating rolling n-period totals, averages or other aggregations

Re: Date range Set Analysis question

alexis — Sat, 05 Apr 2014 17:02:10 GMT

Thanks for responding Gysbert.

I have both of the posts that you have suggested and downloaded the examples listed; I am not sure that AsOfTables is the answer here (or at least I don't think it is).

I have a feeling that there is a very simple answer to my problem here because I am convinced it is a common requirement.... for the period selected (say the quarter Q1) my calculations use the max(date) and min(date) for the result; the application uses these 2 variables to calculate SUMS based on these boundaries - all of that works fine for the SINGLE result for the period selected using . min(date) is 01/01/2014 and max(date) is 31/03/2014.

If the dimension being plotted is months, or weeks then I need to split this period chosen in monthly (or weekly) segments and calculate this value using the pairs 01/01 to 31/01 for min/max of January, 01/02 ... 30/02 for February and so on. It's not a "rolling" kind of issue here.

This problem is really troubling me not so much for its complexity but purely out of frustration because I am sure the answer would be simple...

Thanks

Alexis