Defaulting to Max Date

Hi,

 

We have a field of production date and we want to default it to the max one.

 

E.g.

1/1/2017

1/2/2017

1/3/2017

 

When we open pinboards or searches is there a way to make the selection equal to the max field, 1/3 in this case?

 

Thanks,

5replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi a graz 
    If you want to make all the measures 'max' by default, there is no direct setting for it. You can, however do it on individual columns explicitly.

    This can be done by either manually entering 'max' before a column name in the search bar or selecting 'max' from the small dropdown just below the column header for the measures in the table view (this will also result in putting a 'max' keyword in the search bar before the column name). Now, when this visualization is saved or pinned to a Pinboard, you will always see the max of the respective column.

    In case of measures, flexibility of changing the type of aggregation on the fly has been provided by ThoughtSpot. That is the idea of showing all kinds of aggregations in the visualization dropdown and letting the user choose/change whichever is suitable.

    Like
  • I did select max production date by current balance and it summed the current balance for all production dates, even though it is showing the date. It not technically is filtering by it.  The reason we need this is because there will be pre defined pinboards that should be based off the most recent production date.

    We have formulas where we set now()-2 = production date and what not, but if there is no production date for now()- 2 there will be no data.

     

    Is there any way to get this to work?  

    Like
  • Hi a graz This happens because selecting the max date only applies max to the ‘date’ part of the query and not to the whole dataset. Since the current balance is a measure aggregated by sum, it is shown as a summation in result. In this case, you need to select date by current balance, sort the result by date and select the top 1 of the result in order to apply it to the whole result. Also, if the current balance against the max date is null, it will be shown as null in the result which was your concern. You query here will look like - date current balance sort by date descending top 1.

    Like
  • Nikhita Chandra Thanks,  This worked. I have a follow up with this.  I want to have this date selected, along with previous month ends / quarter ends. e.g.

     

    10/31/2017    balance

    11/30/2017    balance

    12/26(top 1 date)    balance
     

    Like
  • Hi a graz  In order to achieve this, you will need to create an aggregated worksheet from the ‘date current balance’ result. You will then, need tables which contains the quarter end date and balance and the month end dates and balance similar to what we did in https://community.thoughtspot.com/t/631jc5/moving-sum

    These tables need to be joined with our aggregates worksheet created in the first step on quarter end date and the month end date similar to the above post.

    After all the joins have been set up, we can make a search by choosing all the three data sources and selecting ‘date, current balance, last quarter end date, last quarter end balance, last month end date, last month end balance, sort by date descending top 1’ in the search bar. This will give you the desired result.

    If the data is large, I will rather suggest importing this column as part of ETL instead of the above steps to make the performance faster.

    Like
Like Follow
  • Status Answered
  • 2 yrs agoLast active
  • 5Replies
  • 648Views
  • 2 Following