7 Replies Latest reply on Jul 10, 2015 3:04 AM by Mark Fraser

# Last x number of values

Hi, I have started  my free trial today and I am struggling with a very basic condition that I want to apply to one of the attributes. All I want to do is to show only last x number of rows where the measure is not null. I can't even achieve the last x number of rows for an attribute, how can I achieve this?

• ###### 1. Re: Last x number of values

Hi Rafal

You may need table calculations...

There are functions such as ISNULL() for the null test and FIRST(), LAST() and INDEX() functions may all be useful.

You can use RANK()

If you can mock up an example, I or someone can have a look for you

How to attach a packaged workbook >> Attaching a Packaged Workbook

Cheers

Mark

• ###### 2. Re: Last x number of values

Thank You Mark.

I have a dimension table (calendar) where one of the columns used describes year and week (it is string).

This dimension holds the data for last 10 years or so, it starts in 2007 and ends in 2017

Then I have got a measure, let's call it abc and where I have got data only for let's say year.

All I want to achieve is the following:

- show me last 13 weeks where the abc measure is not null. So it is dynamic so every week I run it I want to see last 13 weeks.

So I need to be able to find last non empty week and then pull only last 13 weeks.

Using MDX it would be extremely easy:

tail(filter([calendar_week], not isempty( [abc measure] )),13)

Is it possible to achieve the same in Tableu?

• ###### 3. Re: Last x number of values

It looks as if when I drag and drop my week attribute, the report shows me only those values where the measure is not null so the second part of my problem seems to be solved, however I don't want to see all of the weeks, I only want to see last x number of weeks (10, 20 or any other number)

• ###### 4. Re: Last x number of values

Hi Rafal

Does the number of weeks to look back need to be dynamic? or fixed?

For dynamic - parameters maybe necessary.

Is it something you could mock up and attach?

I can provide a number of weeks to look back via parameter but I don't know how applicable it will be to your data/ situation.

Cheers

Mark

• ###### 5. Re: Last x number of values

Parameters would be nice to have if an user wanted to change the reporting period.

But the basic idea is to show last 13 weeks (automatically)

So if I run this today, I want to see weeks 201501 to 201513,

when I run it next week, I want to see weeks 201502 to 201514

when I run it in two weeks time I want to see weeks 201503 to 201515.

Simple dynamic last 13 weeks. I don't want for the end user to worry about changing parameters. When they open report, I want them to always see last 13 weeks.

• ###### 6. Re: Last x number of values

Hi Rafal

I have quickly mocked something up... I have all dates for 2015, with some daily 'sales' data.

First - I created a parameter called Number of Weeks, type = integer

Next the Filter calculation >>

IF DATEDIFF('week',[Date],DATE('31/12/2015')) = [Number of Weeks] THEN 'SHOW' ELSE 'HIDE' END

NOTE: I'm using a fixed end date, but you could substitute it.

Its testing if the difference in weeks between the sales date and end date is equal to the number entered in the parameter, if it is, then it returns SHOW. In the filter I have set the calculated field to always filter show.

So if we enter 51 it looks 51 weeks back from 31/12/2015 and returns the week 05/01/2015 - 11/01/2015

It may not work exactly as you need but I hope it provides some demonstration of how you may go about it in Tableau.

Cheers

Mark

• ###### 7. Re: Last x number of values

For a fixed time period, you wouldn't need the whole parameter step, just enter a fixed value in the formula.

IF DATEDIFF('week',[Date],DATE('31/12/2015')) = 13 THEN 'SHOW' ELSE 'HIDE' END