6 Replies Latest reply on Jun 11, 2017 8:54 AM by giles.somers.1

    Index T/F when some members do not have all dates - padding to today

    giles.somers.1

      Hi all

      In simple terms, I want to compare last 3 month's orders for each customer vs a historical benchmark

      In general this has been fine, but not all customers have recent data.

       

      Is there any way to have dates automatically filled up to today or the dataset max?

      When I used action filters to look at individual customers, the data range finishes early.

      What this also means is that the t/f filter I have running off an index or date reference also registers early.

       

      Some have suggested data padding with a separate blended file.

      I'm currently testing this, but measure values is not an option - it is greyed out and not able to be selected.


      Would it be that difficult for Tableau to pad out zeros of nulls for dates where there is no data? Is there a better system than adding in data. since this is far from idea.

      Thanks for any pointers

        • 1. Re: Index T/F when some members do not have all dates - padding to today
          Kalyan Allam

          You can find the most recent Order Date for each customer using Fixed LOD.

           

          { FIXED Customer : MAX(Order Date) }

          • 2. Re: Index T/F when some members do not have all dates - padding to today
            giles.somers.1

            Thanks for your reply Kalyan

            Is there a way of using that to 'force' an action-filtered subset into showing the full range of dates? ie. even if the filtered in customer has no data for all of those dates?

            • 3. Re: Index T/F when some members do not have all dates - padding to today
              Kalyan Allam

              If the Customer has no data, then what dates will you show ?

               

              Let me see if i understand what you are trying to achieve,

               

              If you select a date, you want to compare the Customer's Orders for the last 3 months from that date to a historical figure.

              I am assuming that you have Customers in the worksheet, and you have a parameter to select the date.

               

              Lets say you selected today's date in the date parameter and for some of the customers you don't have last one month data. Is that the issue ?  - if it is you can just take the Avg Sales for those Customers till the MAX(Order Date).

               

              If you can share your twbx with some sample data, i will be able to understand the problem more accurately.

              • 4. Re: Index T/F when some members do not have all dates - padding to today
                giles.somers.1

                I'm hoping to show a set of dates up to the current. If there's data, then the dates register that. If no data, then these gaps converted to null /zero.

                What I'm after, is two charts. One has all customers and is used as a control for the action filter. The other, shows one customer at a time.

                 

                The issue is that when I action filter to show certain customers with no recent data, the chart cuts out recent months with no data.

                Since the latest months aren't shown, my index t/f filter that is used to show a last 3 months average, now calculates off a wrong month.

                The calcs are all fine when other customers are present in a broader view to provide all dates, but in the action filtered view, these months don't show.


                I've just tried blending to a full set of dates, but this still gets filtered.

                A similar question would be - how to work out last 3 months average on a customer with no recent data, when shown alone in an action filtered view.

                 

                'Lets say you selected today's date in the date parameter and for some of the customers you don't have last one month data. Is that the issue ?  - if it is you can just take the Avg Sales for those Customers till the MAX(Order Date).'

                I'll test that now, thanks, as I still need a calc like that to generate a label.

                However, is there a way to show a line chart, filtered down to this one customer, with zeros present in recent 'gap' months, rather than the axis just stopping early?

                (I'd love to provide a workbook, but the file is massive and it would take me an age to unpick it without confidential data in)

                • 5. Re: Index T/F when some members do not have all dates - padding to today
                  giles.somers.1

                  This doesn't seem to convert no data to null and then to zero for dates when there is no value present?

                  ZN(LOOKUP(SUM([Value]),0))

                   

                  Is there a way to action filter to a specific customer, but keep zeros showing for dates where there were no orders?

                  • 6. Re: Index T/F when some members do not have all dates - padding to today
                    giles.somers.1

                    Can date parameters be used to force an axis date range?