topic Re: date as dimension and show only last 7 days in QlikView

date as dimension and show only last 7 days

Thu, 05 Sep 2013 13:58:48 GMT

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

swuehl — Thu, 05 Sep 2013 14:02:11 GMT

Try something like

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

Re: date as dimension and show only last 7 days

Thu, 05 Sep 2013 14:24:37 GMT

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

swuehl — Thu, 05 Sep 2013 14:37:57 GMT

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

Thu, 05 Sep 2013 14:48:34 GMT

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

swuehl — Thu, 05 Sep 2013 14:55:29 GMT

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

Thu, 05 Sep 2013 15:01:50 GMT

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

Tue, 09 Aug 2016 15:36:00 GMT

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

Re: date as dimension and show only last 7 days

swuehl — Tue, 09 Aug 2016 18:06:05 GMT

Beatriz,

you can probably use something like shown here:

The As-Of Table

Relative Calendar Fields

Re: date as dimension and show only last 7 days

kavitha_kommise — Wed, 19 Sep 2018 07:52:43 GMT

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

Re: date as dimension and show only last 7 days

dkmanley — Wed, 12 Feb 2020 21:36:04 GMT

this worked great but i changed it to -31 days.

Now I want to only show weekdays Monday - Friday). How would i modify this expression?

=if(SessionsRefreshDate >= today()-31 and SessionsRefreshDate <= today(),SessionsRefreshDate)

in the main section the first weekday is set as

SET FirstWeekDay=6;