Create a default selection for a sheet.

[clarcombe]

Hello,

I have been experimenting with setting a default date for a pivot table using the following variable

=IF(GetSelectedCount(TDB_DT)>0,TDB_DT,Max(TDB_DT))

When the sheet opens, the KPI field shows the latest date, but the actual selection shown is the full range of the data.

I cannot use a bookmark as the date is incremented daily.

How can I set the actual date selection to the Max(TDB_DT) on opening the sheet.

Thanks

[clarcombe]

=IF(GetSelectedCount(TDB_DT)>0,TDB_DT,Max(TDB_DT))

When the sheet opens, the KPI field shows the latest date, but the actual selection shown is the full range of the data.

I cannot use a bookmark as the date is incremented daily.

How can I set the actual date selection to the Max(TDB_DT) on opening the sheet.

[rbartley]

Hi,

Doesn't it work if you add an Action to the sheet where the action is "Select values matching search criteria", the field is the field you are interested in (TDB_DT) and the Value is =Max(TDB_DT)?  It works in my app (FILTER_YEAR field used). See below: 

[stevejoyce]

You can set field selections in bookmarks to be dynamic (based on calculation).  If you search in your filter object like = max({1} TDB_DT = max({1} total TDB_DT), that should update your next day when a new TDB_DT loads in.

[clarcombe]

Unfortunately not, even though the date is showed in the KPI, the full sum of the entire database is being shown in the totals. The first column total should just be 901 not 23283

[clarcombe]

so I added a filter pane and put this expression in, = max({1} TDB_DT) = max({1} total TDB_DT) but it said it was an invalid dimension?

[clarcombe]

I also have a single-date date picker. Is there anyway I can set this selection to a default of the latest date ? Would this be easier

[luis_pimentel]

You can enter that expression in the search box of the TDB_DT listbox (or in the Advanced Search) and then create the bookmark. The search string isn't saved in the bookmark, but the selection that is the result of the search string is. Then set this bookmark as default for the app.

[clarcombe · Accepted solution]

 

I set the start and end date to the max of group 1 and changed the KPI to simply TDB_DT.  This works whenever I change sheets now

If this was what suggested above and I didn't understand it, then kudos to them!!

[stevejoyce]

@luis_pimentel  it's actually the search sting that's saved in the bookmark. it will apply the selection for the result of the search string, but if you do getfieldselection or data changes, the bookmark is the search string.