topic Re: Year filtering to limit dimension in QlikView

Year filtering to limit dimension

Wed, 02 Mar 2016 05:44:51 GMT

Please I need assistance with an expression for a business requirement on how to do a date selection to only show minimum and maximum date for a dimension in a Pivot Table

dimension Name within a cyclic group is [Length of Membership]

Length of membership is described as Date a customer became a member till current date

There are period ranges that was created in the SQL side utilizing CTE

ranges are

<1 Month

2 months

3 Months

4 Months

5 Months

6 Months

7-9 Months

10-12 Months

13-24 Months

>24

I need to be able to select a Year and it filters from that year till current Year. The issue am having is that when I select say Year 2015, I will get results in the Greater than >24 Months category which is incorrect since from January 2015 till March 2016 is less than 24 months

This is what I have so far count({<Year={">=$((Min(OrderDateSK))))<=$((Max(OrderDateSK))"}>} LengthOfMembership)

Re: Year filtering to limit dimension

Gysbert_Wassenaar — Wed, 02 Mar 2016 15:48:23 GMT

Maybe this works: count({<Year={">=$(=Min(OrderDateSK))<=$(=Today())"}>}LengthOfMembership). If not, please post a small qlikview document that illustrates the problem.

Re: Year filtering to limit dimension

Thu, 03 Mar 2016 04:31:40 GMT

Date selection.qvw

<https://drive.google.com/file/d/0B54ItxbqIZdTVE1PQy1wVVgyZEE/view?usp=drive_web>

Hello Gysbert

Thanks for your response to my question. much appreciation. The expression

didn't work so I am attaching a QVW file per your request.

Thanks

Jay

On Wed, Mar 2, 2016 at 9:49 AM, Gysbert Wassenaar <qcwebmaster@qlikview.com>

Re: Year filtering to limit dimension

Gysbert_Wassenaar — Thu, 03 Mar 2016 07:32:57 GMT

Can you attach the qvw file to this discussion instead of posting a link to unknown territories?

Re: Year filtering to limit dimension

Thu, 03 Mar 2016 13:17:18 GMT

Hello

The requirement is to select a year and the result should filter from that year till current. selecting for instance 2015 should filter every customer from 2015 till current which should be between 13-14 months category. But the issue is that any year selected seems to have data in the 24 months category.

see attached

Thanks

Re: Year filtering to limit dimension

Gysbert_Wassenaar — Thu, 03 Mar 2016 17:05:41 GMT

Ok, I didn't specify any size when I asked for small. So, can you post something that's not larger than 5 MB? Around 1 MB would be even better.