Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Mahamed_Qlik
Creator II
Creator II

previous month data

Hi

 

I want to show the employee on the leave.  I have their Leave_startdate and Leave_End_Date.
So, when I select the year and month then the total number of leave during that months counts needs to be shown:

I have data as below:

Emp_No  Start_dt                      End_date                 No_Leave_Days

100             31/08/2022              02/08/2022            2

100             05/08/2022               10/08/2022          5

 

In above data employee 100 has taken leave 2 times. First is from (31/07 to 02/08) which is 2 days and 

second from (05/08 to 10/08)   which is 5 days...

so I want to show detail report in table object but issue is
when I select month as aug (08) then it is showing only second record but if you can see data it has leave on 01/08/2022 as well so this record is not showing.

 

 

 

Labels (1)
6 Replies
BrunPierre
Specialist II
Specialist II

Try with this date-level expression with the date your month field was derived from.

Sum({<Year=, Quarter=, Month=, Week=, [Date Field]={">=$(=MonthStart(Max([Date Field]))) <=$(=Max([Date Field]))"}>}No_Leave_Days)

Mahamed_Qlik
Creator II
Creator II
Author

Hi Brun

This is always returning month start of each month that is range will always selected from first of the month.
I have another case 

Start date is 21/08/2022 and end date is 05/09/2022 : In this case if I select month as sep then your logic is working fine because it will return  number of days from 01/09/2022 to 05/09/2022 but if I am selecting month as Aug then it is picking up range from 01/08/2022 to 31/08/2022 however it needs to return from 21/08/2022 to 31/08/2022

BrunPierre
Specialist II
Specialist II

Which date is your month derived from?

Mahamed_Qlik
Creator II
Creator II
Author

Month is derived from another TRANS_DATE from other fact table which is then created master calendar on same

Mahamed_Qlik
Creator II
Creator II
Author

I will make it more easy

We have 2 fact table 

Fact 1 has two date field which is start_date and end_date and another fact has one date field which is trans_date

both the fact table has key field in bewteen them as ID.

I have created master calendar using Trans date from which I have derived my mcal_date, mcal_month and mcal_year

Now on front end when user will select mcal_month then  during selected month needs to calculate the number of days using start_date and end_date.

coming back to data now :

Emp_No  Start_dt                      End_date                 

100             28/07/2022              02/08/2022            

100             05/08/2022               10/08/2022 

 

here if user select te mcal_month as    "July" then for the first record the end date should be as 31/07/2022 and then number of days will be end_date-start_date , 31/07/2022-28/07/2022 =  3 days and if user will select mcal_month as "Aug" then first record the start date will be 01/08/2022 an end_date will be 02/08/2022 = 1 day 
and second record will be 5 days





BrunPierre
Specialist II
Specialist II

What if the leave start and end dates were separated from the master calendar and then assigned variables? It allows the user to choose between 2 arbitrary dates.

Perhaps these might be helpful;

Enter Date From To 

Selecting Arbitrary Date Ranges - YouTube