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
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?
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)
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.
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.
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.
177945.twbx 20.1 KB
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