Qlik Community

QlikView Expressor

Discussion Board for collaboration related to QlikView Expressor.

jim_chan
Contributor III

how to create a "Today, Yesterday,Last Week, Last Month" List Box.

Hi all,

Due to design point of view, i need to create a list box, where user can choose to select specific period and display the data. and what my user wants is... a

"Today, Yesterday,Last Week, Last Month" List Box.

any examples qvw file out there?

Rgd,

Jim

7 Replies
Highlighted
MVP
MVP

Re: how to create a "Today, Yesterday,Last Week, Last Month" List Box.

Hi,

Try like this if you have a date field

LOAD

*,

If(DateFieldName = Today(), 'Today',

     If(DateFieldName = Date(Today() - 1), 'Yesterday',

     If(WeekName(DateFieldName)= WeekName(Today(), -1), 'Last Week',

     If(MonthName(DateFieldName) = MonthName(Today(), -1) , 'Last Month')))) AS New_FieldName

FROM DataSource;

Hope this helps you.

Regards,

jagan.

jim_chan
Contributor III

Re: how to create a "Today, Yesterday,Last Week, Last Month" List Box.

wokay Jagan! let me try it out, and i will let you know !

Thanks!

Re: how to create a "Today, Yesterday,Last Week, Last Month" List Box.

In addition to what jagan‌ provided, you can also use Pick() and Match() function also:

Pick(Match(DateFieldName, Today(), Today()-1, WeekName(Today(), -1), MonthName(Today(), -1)), 'Today', 'Yesterday', 'Last Week', 'Last Month') as New_FieldName

jim_chan
Contributor III

Re: how to create a "Today, Yesterday,Last Week, Last Month" List Box.

Hello Jagan,

I added into my master calendar script, and it didnt work.

Calendar:

load

Distinct

//MonthEnd(TempDate) as Main_Date,

Date(TempDate) as Date,

Year(TempDate) as Year,

Month(TempDate) as Month,

Day(TempDate) as Day,

num(Month(TempDate)) as MonthNo,

'W' & Week(TempDate) as Week,

Date(monthstart(TempDate),'YYYYMM') as MonthYear,

'Q' & Ceil(Month(TempDate)/3) as Quarter,

If(TempDate = Today(), 'Today',

     If(TempDate = Date(Today() - 1), 'Yesterday',

     If(WeekName(TempDate)= WeekName(Today(), -1), 'Last Week',

     If(MonthName(TempDate) = MonthName(Today(), -1) , 'Last Month')))) AS MyDateSelection

Resident TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

Re: how to create a "Today, Yesterday,Last Week, Last Month" List Box.

This seems to be working:

Pick(Match(Num(TempDate), Num(Today()), Num(Today()-1), Num(WeekName(Today(), -1)), Num(MonthName(Today(), -1))), 'Today', 'Yesterday', 'Last Week', 'Last Month') as New_FieldName


Capture.PNG

MVP
MVP

Re: how to create a "Today, Yesterday,Last Week, Last Month" List Box.

Hi,

What is the issue? You get error or you do not get any values in the fields?

check this

Calendar:

load

Distinct

//MonthEnd(TempDate) as Main_Date,

Date(TempDate) as Date,

Year(TempDate) as Year,

Month(TempDate) as Month,

Day(TempDate) as Day,

num(Month(TempDate)) as MonthNo,

'W' & Week(TempDate) as Week,

Date(monthstart(TempDate),'YYYYMM') as MonthYear,

'Q' & Ceil(Month(TempDate)/3) as Quarter,

If(Date(TempDate) = Today(), 'Today',

     If(Date(TempDate) = Date(Today() - 1), 'Yesterday',

     If(WeekName(TempDate)= WeekName(Today(), -1), 'Last Week',

     If(MonthName(TempDate) = MonthName(Today(), -1) , 'Last Month')))) AS MyDateSelection

Resident TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

Regards,

jagan.

Digvijay_Singh
Honored Contributor III

Re: how to create a "Today, Yesterday,Last Week, Last Month" List Box.

Jagan's added script is working properly-

Capture.JPG

Community Browser