previous month data

[Mahamed_Qlik]

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.

[BrunPierre]

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]

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]

Which date is your month derived from?

[Mahamed_Qlik]

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

[Mahamed_Qlik]

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]

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