topic Re: Find out how many days fall between start and end date based on month year selected in App Development

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

hopkinsc — Thu, 25 May 2023 22:19:27 GMT

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

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

Kushal_Chawda — Thu, 25 May 2023 22:59:15 GMT

@hopkinsc may be below

=if(GetSelectedCount(MonthYear)=1, rangemin(End, monthend(max(MonthName))) - rangemax(Start, monthstart(max(MonthName))), End-Start)

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

hopkinsc — Tue, 30 May 2023 11:11:27 GMT

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

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

Kushal_Chawda — Tue, 30 May 2023 11:17:01 GMT

@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

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

hopkinsc — Tue, 30 May 2023 13:35:23 GMT

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.

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

hopkinsc — Tue, 30 May 2023 15:25:47 GMT

I know have it working, thanks for your help.