Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
yura_ratu
Partner
Partner

How to find next/previous value of the field?

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

1 Solution

Accepted Solutions
stigchel
Partner
Partner

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

View solution in original post

5 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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
Partner
Partner

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

View solution in original post

yura_ratu
Partner
Partner
Author

Hi Piet Hein,

Great solution and explanation, thank you a lot!

yura_ratu
Partner
Partner
Author

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
Partner
Partner

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)