Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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)

View solution in original post

10 Replies
swuehl
MVP
MVP

Try something like

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

Not applicable
Author

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

swuehl
MVP
MVP

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)

Not applicable
Author

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

swuehl
MVP
MVP

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

Not applicable
Author

yes and it works with suppress when value is null

many many thanks! i spent some hours on this issue

Not applicable
Author

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.

Thanks in advance

swuehl
MVP
MVP

Beatriz,

you can probably use something like shown here:

The As-Of Table

Relative Calendar Fields

kavitha_kommise
Partner - Contributor III
Partner - Contributor III

hello Stefan,

I have a requirement populate values for previous three months excluding current month in pivot table month-year as a dimension.how can i achieve this table.

Excepted output should be

Month_year    aggr1  aggr2 aggr3

sep-2018       34          23    23

aug-2018       77          34    45

based on month-year it has to show previous 3 months sum.for example for sep it has to give

aggregation of jun,july,aug.like for all months it has to show.

please help me on same.

Thanks in advance

kavitha