Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
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

Tags (1)
1 Solution

Accepted Solutions
Partner
Partner

Re: How to find next/previous value of the field?

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
MVP & Luminary
MVP & Luminary

Re: How to find next/previous value of the field?

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

Partner
Partner

Re: How to find next/previous value of the field?

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

Partner
Partner

Re: How to find next/previous value of the field?

Hi Piet Hein,

Great solution and explanation, thank you a lot!

Partner
Partner

Re: How to find next/previous value of the field?

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

Partner
Partner

Re: How to find next/previous value of the field?

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)