topic Re: Return minimum date from the next day in QlikView

Return minimum date from the next day

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I want to return the minimum time value from the next day in a straight table (date is used as one of the dimensions)

I've searched the community and come up with the following set analysis

min({<Date={"$(=max(Date)+1)"}>}Time)

It however doesn't seem to work. Could anyone assist me in where I'm going wrong?

Re: Return minimum date from the next day

Gysbert_Wassenaar — Tue, 14 Aug 2018 11:50:15 GMT

$(=max(Date)) is evaluated at the document level, so it returns the absolute maximum date possible given the active selections. This is likely the overall maximum date. You then tell the expression to calculate a set where the Date is the maximum Date + 1. This is a Date that does not exist in the possible Date values given the selections. So the result is an empty set.

And there's a good chance that Date has a date format while max(Date) returns a number so there won't be any match and the result is then an empy set.

Also you also use Date as a dimension in your chart. The set calculated by the set analysis expression is intersected with the set calculated by the set analysis expression. So for any row in the table Date has the value of the Date dimension for that row and must also have the value that's calculated by the set analysis expression. If those are different values (as you would like to do with the next day for a date) the result is an empty set.

As a solution you could create a small extra table with an extra Date dimension. That new Date dimension would be linked to two date values in the original Date dimension, the current date and its tomorrow.

Re: Return minimum date from the next day

Anonymous — Tue, 14 Aug 2018 11:55:25 GMT

Thanks Gysbert, I think that makes sense to me!

So my next question is, how could I return the minimum of the next day? Is it as simple as min(time)+1?

or is this not possible?

Re: Return minimum date from the next day

Gysbert_Wassenaar — Tue, 14 Aug 2018 12:04:15 GMT

AsOf:

LOAD DISTINCT

Date as Date_AsOf,

Date + IterNo() -1 as Date,

IterNo() -1 as DateDiff

RESIDENT

...table with dates and times...

WHILE

IterNo() <=2

;

Then you can use Date_AsOf as chart dimension. In your expressions you need add some set analysis magic to make things keep working. The Date_AsOf would make the expressions calculate results over two Date values, the date and its tomorrow. So you'd change for example sum(Sales) to sum({<DateDiff={0}>}Sales).

If you use a listbox to let the user make selections in the Date dimension then you'd need something to make that selection be applied to Date_AsOf: sum({<DateDiff={0}, Date=, Date_AsOf=P(Date)>}Sales).

And to calculate your Time you'd use: min({<Date, Date_AsOf=P(Date), DateDiff={1}>}Time)

Re: Return minimum date from the next day

jyothish8807 — Tue, 14 Aug 2018 12:06:38 GMT

Hi Jamie,

What do you mean by Next Day ? Do you have data for next day in your data set ? Are you referring to today as current dater and trying to find the next date ?

Br,

Re: Return minimum date from the next day

Anonymous — Tue, 14 Aug 2018 12:31:17 GMT

Yes, I have a set of date/times.

I'm extracting the date to use as the dimension for my straight table and then based on certain criteria want to return either maximum date/time of the same day or the minimum date/time of the next day.

Re: Return minimum date from the next day

jyothish8807 — Wed, 15 Aug 2018 06:31:19 GMT

Hi Jamie,

Try the attached sample, may be you get some idea out of it.:)

Br,