Find out how many days fall between start and end date based on month year selected

[hopkinsc]

Hi all,

I need to find a way to get the number of days between a start and end date based on the selected monthyear. 

So, if i have a row of data with a start date of 01/01/2022 and an end date of 16/04/2022 and the user selected April 2022, then i want to see that for this row in particular, there are 16 days that fall within that monthyear selection. 

It seems like it should be quite easy but i have no idea where to start.

I have attached a QVW with some sample data that I've put together. and the expected output for this dummy data when April-2022 is selected is..

Is anyone able to help please?

Thanks in advance

[Kushal_Chawda · Accepted solution]

@hopkinsc  may be below

=if(GetSelectedCount(MonthYear)=1,

rangemin(End, monthend(max(MonthName)))
-
rangemax(Start, monthstart(max(MonthName))),

End-Start)

[hopkinsc]

Hi @Kushal_Chawda 

that's great, it works as expected in QlikView. I need this to work in Qlik Sense though, but the same expression doesn't work. It looks like rangemin and rangemax should work in Qlik Sense but for some reason it isn't. Do you have any ideas? 

rangemin(End, monthend(max(MonthName)))
-
rangemax(Start, monthstart(max(MonthName)))

Thanks

[Kushal_Chawda]

@hopkinsc  This should work in qlik sense as well.  Make sure that you are using MonthName which is actual monthyear format on which you can apply the max. If you can share sample qvf I can look into it

[hopkinsc]

Hi @Kushal_Chawda 

Thanks, I am using a monthyear field that i have in the data but I'm getting wrong results. 

I have attached a QVF with actual field names that are in my data.  I've also added a text box with a description of what is needed and 2 examples. 

Thanks for your help

edit: 

apologies, I uploaded the wrong QVF. The correct one has now been added. 

[hopkinsc]

I know have it working, thanks for your help.