topic Re: List field with date in QlikView

List field with date

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

Hello,

I need to display a list with the last 30 dates known. Here is an example:

Please select a date
01/03/2018
28/02/2017
...
01/02/2017

So, I have a date dimension and need to limit this to the last 30 days.

I tried using an If structure to limit the results but it doesn't work since all dates available is shown.

=If(Date>=(Date-30),Date)

Thanks for your advise!

Re: List field with date

Miguel_Angel_Baeyens — Fri, 02 Mar 2018 10:57:00 GMT

If those are 30 dates starting today, then

If(Date >= Today() -30, Date)

Anyway, I would create a flag field or a different field in your calendar table and use this in the frontend.

Check the AsOf table concept: The As-Of Table

Re: List field with date

tresB — Fri, 02 Mar 2018 10:58:04 GMT

May be this?

=If(Date>=$(=Max(Date))-30,Date)

Re: List field with date

edemerdjieva — Fri, 02 Mar 2018 11:02:26 GMT

It doesn't work. All dates are displayed.

Re: List field with date

edemerdjieva — Fri, 02 Mar 2018 11:03:09 GMT

Date corresponds to sales dates. It's not just a calendar.

Re: List field with date

tresB — Fri, 02 Mar 2018 11:08:47 GMT

Are your dates real-dates or texts? Could you post a screen shot?

Re: List field with date

edemerdjieva — Fri, 02 Mar 2018 11:16:11 GMT

The dates are texts which is formatted with a Date(Date) function.

Re: List field with date

tresB — Fri, 02 Mar 2018 11:22:54 GMT

Date() is just a formatting function and it can't convert text into a number/real-date value. Rather use Date#() to parse your date values during load. Then the expression shared above should work fine at the UI.

Load

Date#(yourTextDateField, 'dateformat <how it looks as text>') as Date

Re: List field with date

Qrishna — Fri, 02 Mar 2018 11:56:38 GMT

Hi - check out this.

the script uses max function to find out 1st max date,2nd max date etc.

you can change the field names or script, as per your needs.

the same logic applies for your sales date field too.

if you can provide the sample data, it would be good for us to play around with it.