Limiting the date as a calculated dimension

[#1]

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

[brenner_martina]

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

[whiteline]

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

[#4]

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.

[whiteline]

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

[#6]

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.

[whiteline]

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)

[#8]

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

[whiteline · Accepted solution]

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)

or

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

correspondingly.

[#10]

Thanks for your help!