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

    Last x number of values

    rafal.lipczyk

      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
          Mark Fraser

          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
            rafal.lipczyk

            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
              rafal.lipczyk

              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
                Mark Fraser

                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
                  rafal.lipczyk

                  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
                    Mark Fraser

                    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
                      Mark Fraser

                      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