How to find next/previous value of the field?

[yura_ratu]

Hi Community,

I have dashboard with data showing always by 1 date. For easy navigation between date I've made these buttons

where buttons make just +/- 1 or min/max to selected date. The issue is - there are gaps in the data, so there isn't data for every day. Once users stuck on such date, Date filter just get vanished.

Is there any possibility to find next date with data, instead of just making + or - 1 day to selected date?

Thanks

[marcus_sommer]

Before finding the next possible value you should think if it's could not better to fill the gaps within the datamodel by using from a master calendar: The Master Calendar. Further I'm not sure if it's necessary to build this with buttons/actions instead of using from a calendar-box.

A way to find to find the next possible value could be the function fieldvalue() which picked the n-distinct value from a field.

- Marcus

[stigchel · Accepted solution]

For the case where you would like to select a next date with data in the fact table, you can maybe use something like

=SubField(Concat({<Date={">$(=Date)"},Value={">0"}>} DISTINCT Date,'@',Date),'@',1)

It uses the Concat function with a date greater then the current selected date and a value >0 sorted by date. The SubField function selects the first possible next date out of these values

Example attached

[yura_ratu]

Hi Piet Hein,

Great solution and explanation, thank you a lot!

[yura_ratu]

Just to add, to select previous date with data:

=SubField(Concat({<Date={"<$(=Date)"},Value={">0"}>} DISTINCT Date,'@',Date),'@',Count({<Date={"<$(=Date)"},Value={">0"}>} DISTINCT Date))

[stigchel]

I thought you could work that out...

One extra tip, you could also reverse the sort order and still use SubField 1, e.g.

=SubField(Concat({<Date={"<$(=Date)"},Value={">0"}>} DISTINCT Date,'@',1/Date),'@',1)