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: 
blunckc1
Creator
Creator

Return employee contracts within a month

Hi folks,

I have a table of employee contracts that all have a start and end date and I want to show these in a straight table based on a MonthYear selection.

Created a master table that is linked through the start date field so at present, when I select a MonthYear, the straight table shows records which have a start date in that month.

But what I want the table to show is all of the contracts that exist in this month.  Thought this would be done through a calculated condition as follows:

If(MonthStart([Start Date])<=MonthYear and MonthStart([End Date])>=MonthYear)

But I am getting an error in expression return for the last ')' and I can't work out why?!

Is this even the right way to go about this?

Cheers

Carl

4 Replies
rittermd
Master
Master

Can you share your app?

Gabriel
Partner - Specialist III
Partner - Specialist III

Hi,

I think you need this function in the script.

InMonthToDate (timestamp, base_date, period_no)


IF(INMONTHTODATE(([Start Date],TODAY(),0),1,0)                    AS [Flag Contract In Month To Date]

Then your Set Aanlysis is simpler,

i.e

COUNT({<[Flag Contract In Month To Date] ={1}>} EmpID)

Something like that

Hope this help

blunckc1
Creator
Creator
Author

I will need to strip the data out that isn't my personal information, which will take some time...

blunckc1
Creator
Creator
Author

Thanks Gabriel, but I don't think so.  Only because the base_date will need to change based on what MonthYear is selected by a user in the front end.