8 Replies Latest reply: Aug 9, 2016 2:06 PM by Stefan Wühl

# date as dimension and show only last 7 days

hi all,

i have a pivot table with names on rows and dates on columns and i want to see only last 7 days (in this dimension i have current month, last month and current month from last year - day by day)

and calculated expression like sum (sales) for eg

1 sep 2012 .....30 sep 2012    1 aug 2013..31 aug 2013  1..4 sep 2013

john

paul

alma

so what i want is:

29aug2013  30aug 2013  31aug 2013  1sep 2013    2sep 2013  3sep 2013 4 sep 2013

john

paul

alma

date format is 9/4/2013

thanks

• ###### Re: date as dimension and show only last 7 days

Try something like

=sum({<DateField = {">=\$(=today()-7)<\$(=today())"}>} Value)

• ###### Re: date as dimension and show only last 7 days

i want to reduce the date dimension only with some last days (7 days) in pivot table not to calculate a sum of them

• ###### Re: date as dimension and show only last 7 days

I understand this.

Using a set expression in all your expressions should limit your date dimension value to the last 7 days. Have you already tried it? What expression(s) are you using?

You can also try a calculated dimension instead the set expression(s):

=if(DateField >= today()-7 and DateField < today(), DateField)

• ###### Re: date as dimension and show only last 7 days

it works

une issue remained; it appeared only last 7 days as dimension but at the end it appears a column with the rest of data; do you know how to eliminate this?

many thanks

• ###### Re: date as dimension and show only last 7 days

You are using the calculated dimension? Then check the option 'Suppress When Value is NULL' on dimension tab.

• ###### Re: date as dimension and show only last 7 days

yes and it works with suppress when value is null

many many thanks! i spent some hours on this issue

• ###### Re: date as dimension and show only last 7 days

Hello Stefan,

I have a similar question to this issue. My issue is the same. I have "Month" as dimension, and I want to limit this dimension only shoing the last "3 months+ current month", In this case, I would like to show the data for the months of May,June,July and August.

I would appreaciate if you could guide me with this question.