Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Architect
Creator
Creator

qlikview date and month

HI, I have two questions-

1>There is a table with 2 columns employee_name and DOB
for any given day how to determine how many employees have birthdays in the coming 30 days?

2> For any given day how to determine what months we may have in the coming 30 days?
say today is April 25th and in the coming 30 days, we have both April and May.

Labels (1)
1 Solution

Accepted Solutions
Vegar
Partner
Partner

If you don't want to adjust your data model then you could try this.

No of birthdays next 30 days
count({<DOB={"=Age('$(vL.Today)',DOB)<Age('$(vL.Today)'+30,DOB)"}>}DOB)

Months in next 30 days
IF (month( '$(vL.Today)')=month( '$(vL.Today)'+30),
Date( '$(vL.Today)', 'MMMM'),
date( '$(vL.Today)', 'MMMM')& ', '& date( '$(vL.Today)'+30, 'MMMM')
)

Vegar_0-1619333104345.png

Please find attached qvw

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

4 Replies
Vegar
Partner
Partner

If you don't want to adjust your data model then you could try this.

No of birthdays next 30 days
count({<DOB={"=Age('$(vL.Today)',DOB)<Age('$(vL.Today)'+30,DOB)"}>}DOB)

Months in next 30 days
IF (month( '$(vL.Today)')=month( '$(vL.Today)'+30),
Date( '$(vL.Today)', 'MMMM'),
date( '$(vL.Today)', 'MMMM')& ', '& date( '$(vL.Today)'+30, 'MMMM')
)

Vegar_0-1619333104345.png

Please find attached qvw

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Architect
Creator
Creator
Author

@Vegar  thank you so much for the answers.

I always struggle with this date function. if you can guide me to any document or link that can explain this function clearly, would be of great help.

 

 

Vegar
Partner
Partner

I don't have a good for you at the moment, but I see that a lot of people missunderstand the date() function. It is a formating function that presents a numeric value as a date in default or specified format. In my case 'MMMM' which is the full month name. It is important to remember that the underlying numeric value will not change when using date().

Consider this
The timestamp '2021-04-25 09:57:00' it has the numeric value of 44311.414583333.  
The date '2021-04-25' has the numeric value of 44311.
Using Date() on a timestamp will not change the underlying numeric value. So Date('2021-04-25 09:57:00') will present the string '2021-04-25', but the numeric value will still be 44311.414583333 

To convert a timestamp or date into another value I recommend looking into the date and times functions offered in QlikView

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Architect
Creator
Creator
Author

@Vegar  much appreciated.

Thanks a lot.