topic Re: Limiting the date as a calculated dimension in QlikView

Limiting the date as a calculated dimension

Fri, 07 Sep 2012 13:01:39 GMT

Hi I currently have a calculated dimension which returns me all the possible issue dates.

=Date(ISSUE_DATE,'dd MM YYY')

How can I limit this to only show the last 13 ?

Can anyone help???

Thanks

Re: Limiting the date as a calculated dimension

brenner_martina — Fri, 07 Sep 2012 14:01:24 GMT

Hi, rms,

you can use every function, p.e. Min() and Max(), AddMonths() etc. and ofcourse you can add dates:

If(Num(ISSUE_DATE)-13 <= Num(ISSUE_DATE), Date(ISSUE_DATE,'DD MM YYYY'))

Greetings from Munich

Martina

Re: Limiting the date as a calculated dimension

whiteline — Sat, 08 Sep 2012 15:04:18 GMT

=If(Num(ISSUE_DATE) >= Num(Max(total ISSUE_DATE))-13, Date(ISSUE_DATE,'DD MM YYYY'))

Re: Limiting the date as a calculated dimension

Sun, 09 Sep 2012 09:35:24 GMT

Hi Guys

I have tried the expressions you suggested but still having no luck as when certain products that are selected the pivot table still displays more than 13 issue dates.

Re: Limiting the date as a calculated dimension

whiteline — Sun, 09 Sep 2012 10:25:03 GMT

Could you provide a small sample (.qvw or excel input data)?

Re: Limiting the date as a calculated dimension

Sun, 09 Sep 2012 12:17:21 GMT

Hi whiteline

As its customer data I am unable to attach the app. In other charts there is the option to change the dimension limits but as this is a pivot there isn't the option to do that.

All I need to display is the 13 latest most recent 'ISSUE_DATE'

I have tried creating a variable: vMaxIssueDate = Max(date(ISSUE_DATE))-13 and using that in the expression but no luck with that either. As it just gets the date and subtracts 13 days from it not 13 whole ISSUE_DATES from the list.

Re: Limiting the date as a calculated dimension

whiteline — Sun, 09 Sep 2012 12:31:14 GMT

Try this as calculated dimension:

=aggr(If(Num(ISSUE_DATE) >= Num(Max(total ISSUE_DATE))-13, Date(ISSUE_DATE,'DD MM YYYY'), null()), ISSUE_DATE)

Re: Limiting the date as a calculated dimension

Sun, 09 Sep 2012 12:37:32 GMT

I have replicated the table in another test app which i have attched. In this instance it will be the field 'Natural_Month' where I would like to limit the first 7 dates.

Hope this helps

Thanks

Re: Limiting the date as a calculated dimension

whiteline — Sun, 09 Sep 2012 13:03:21 GMT

You can use this expression as calculated dimension (second):

=aggr(If(Natural_Month >= Max(total Natural_Month)-7, Natural_Month, null()), Natural_Month)

and toggle 'Supress when value is null' check box.

Or you can use setanalysis in your expression instead:

=sum({$<Natural_Month={"=Natural_Month>=Max(total Natural_Month)-7"}>}Sales)

the first 7 dates

if you mean the first from the lowest one then use this:

=aggr(If(Natural_Month <= Min(total Natural_Month)+7, Natural_Month, null()), Natural_Month)

=sum({$<Natural_Month={"=Natural_Month<=Max(total Natural_Month)+7"}>}Sales)

correspondingly.

Re: Limiting the date as a calculated dimension

Thu, 20 Sep 2012 11:45:52 GMT

Thanks for your help!

Re: Limiting the date as a calculated dimension

whiteline — Thu, 20 Sep 2012 13:15:20 GMT

Could you mark the answer as right and close the question ?