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

How to find next/previous value of the field?

yura_ratu — Mon, 27 Apr 2015 10:07:02 GMT

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

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

marcus_sommer — Mon, 27 Apr 2015 10:35:49 GMT

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

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

stigchel — Mon, 27 Apr 2015 11:52:13 GMT

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

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

yura_ratu — Mon, 27 Apr 2015 12:08:02 GMT

Hi Piet Hein,

Great solution and explanation, thank you a lot!

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

yura_ratu — Mon, 27 Apr 2015 12:22:54 GMT

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

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

stigchel — Mon, 27 Apr 2015 12:47:11 GMT

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)