Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
terezagr
Partner - Creator III
Partner - Creator III

Current date to last year date when weekend data for last year not exist

Hi all,

I have two list boxes: Date 1 (alternate state Year 1) and Date 2 (alternate state Year 2).

Every time when a user click on a button I want the current date in Date 1 to be selected and the same date but for last year in Date 2 to be selected. (Date 1: 13-03-2014 and Date 2:13-03-2013)

Now, this seems like an easy task, although it is not that easy if your snapshots from which you get these dates have run only 6 times a week Mon-Sat for the past 4 months and before that the snapshots run only 5 times a week Mon-Fri. Therefore if you open your application on Monday and click on the button,  you want the date in Date 1 to be the current date and the date in Date 2 to be set the current date - 2 days. Example: Application opened on Monday 10-03-2014, Date 1 will be 10-03-2014, but because this date last year was sunday and on saturday the snapshot also did not run, you want in Date 2 to be selected date 08-03-2013.

I have tried following settings on button:

1)

Select in field

Field: date

Search String=max(date)

Alternate State=Year 1

Toggle Select

Field: date

Search String: =date(addyears(max({[Year 1]}date), -1), 'DD/MM/YYYY')

Alternate state: Year 2

//this will select the date when the comparison snapshots exist

Toggle Select

Field: date

Search String  =date(num(mid(DateMax,1,10))-367)

Alternate state: Year 2

//this will select the date-2 when the snapshot for last year does not exist

This works until it comes to Thursday and then in Date 2 list box are selected dates 13-03-2013 and 11-03-2013

2)

Select in field

Field: date

Search String=max(date)

Alternate State=Year 1

Select in Field

Field: date

Search String: =if(WeekDay(date)=6,date(num(mid(DateMax,1,10))-367), date(AddYears(max({[Year 1]}date), -1), 'DD/MM/YYYY'))

Alternate state: Year 2

Works for case when dates Date 1:13/03/2014-13 and Date 2:13/03/2013, not for dates Date 1:10/03/2014 and Date 2:08/03/2013

Any suggestions?

Thanks for your help in advance!

T.

1 Solution

Accepted Solutions
terezagr
Partner - Creator III
Partner - Creator III
Author

If anyone would have ever the same problem, this is how I solved it:

=if((WeekDay(date(AddYears(max({[Year 1]}date), -1), 'DD/MM/YYYY'))='Sun'), date(num(mid(DateMax,1,10))-367), date(AddYears(max({[Year 1]}date), -1),'DD/MM/YYYY'))


Like this the function will look on the date last year and if this is sunday, then it will do -2 days of current day, else current date last year.


T.

View solution in original post

1 Reply
terezagr
Partner - Creator III
Partner - Creator III
Author

If anyone would have ever the same problem, this is how I solved it:

=if((WeekDay(date(AddYears(max({[Year 1]}date), -1), 'DD/MM/YYYY'))='Sun'), date(num(mid(DateMax,1,10))-367), date(AddYears(max({[Year 1]}date), -1),'DD/MM/YYYY'))


Like this the function will look on the date last year and if this is sunday, then it will do -2 days of current day, else current date last year.


T.