topic Re: Date expression (current date to month end date) in QlikView

Date expression (current date to month end date)

inam — Mon, 15 May 2023 05:50:12 GMT

Hello community,

In holiday forward booking count Field I want date expression( today to end of month is the Forward booking).

below i used this expression but not getting answer

= Count({<TOUR_START_DATE ={"(=Today())"}>}TOUR_START_DATE)

			today to end month
Product	FILE_TYPE	Holiday Backward Booking	Holiday Forward Booking count
Domestic	FIT	2638	0
	GIT	1589	0
	Total	4227	0
International	FIT	5316	0
	GIT	10488	0
	Total	15804	0

I am using only Tour_Start_Date with that I have calulate this.

Re: Date expression (current date to month end date)

MayilVahanan — Mon, 15 May 2023 06:12:41 GMT

First of all, ur date column looks like Text, because, generally, if its date / num format, it should be in right side.

So, can you try to change it from Text to date format like

Load Date(Date#(TOUR_START_DATE, 'urdateformat'), 'urdateformat') as TOUR_START_DATE, .. from urdatasource;

Exp:
count({$<TOUR_START_DATE={">=$(=Date(Today()))<=$(=Date(floor(MonthEnd(TOUR_START_DATE))))"}>}
DISTINCT TOUR_START_DATE)

Re: Date expression (current date to month end date)

inam — Mon, 15 May 2023 07:57:28 GMT

Thank yo so much MayilVahanan.

now expression is working fine

but I have one more doubt

if I use this

= Count({$<TOUR_START_DATE={">=$(=Date(Today()))<=$(=Date(floor(MonthEnd(TOUR_START_DATE))))"}>}DISTINCT TOUR_START_DATE) // ans is 98

if I use this = Count({<TOUR_START_DATE = {">$(=Today())"}>} TOUR_START_DATE) // ans is 361

but the Correct Ans is 174

Re: Date expression (current date to month end date)

MayilVahanan — Mon, 15 May 2023 08:47:21 GMT

Its depends on , how you want to count the values either distinct date or all dates.

= Count({$<TOUR_START_DATE={">=$(=Date(Today()))<=$(=Date(floor(MonthEnd(TOUR_START_DATE))))"}>}DISTINCT TOUR_START_DATE) -- its calculate distinct count

= Count({$<TOUR_START_DATE={">=$(=Date(Today()))<=$(=Date(floor(MonthEnd(TOUR_START_DATE))))"}>} TOUR_START_DATE) -- it calculates the no. of count incude duplicate

= Count({<TOUR_START_DATE = {">$(=Today())"}>} TOUR_START_DATE) -- it calculate from greater than Today to end of the dates in the data model