Date range Set Analysis question

[alexis]

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

[alexis]

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!

[alexis]

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

[#4]

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. 😃

[Gysbert_Wassenaar]

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.

[alexis]

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

[Gysbert_Wassenaar]

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

[alexis]

Hi Gysbert,

The attached application example should explain what I mean

Thanx

Alexis

[Gysbert_Wassenaar]

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

[alexis]

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