Days from dates in qlikview expression

Question

I am trying to get days from given data like this:

In this data suppose ID B start date is 4/10/2019 and end date is 10/25/2019. Here there is 7 months: April to October, so for the first month start date is 4/10/2019 and end date is 4/30/2019 so this means he only avail 10 days from this month and remaining days is 21.. same for here end date is 10/25/2019 so if we look calendar end date 10/31/2019 we only avail 6 days so in data I want to get above data which is mentioned in image .. where as I am trying this formula in qlikview:

=sum(
  If(
    MonthName(CalendarMonthEnd) = MonthName([End Date]),
    ([End Date]-CalendarMonthStart+1),
    (RangeMin([End Date],CalendarMonthEnd)-RangeMax([Start Date],CalendarMonthStart))
  )
)

and through this formula I get this data which is remaining days where i want to get days which is availed...

this is link of folder please download and check ..

https://www.dropbox.com/s/v48373io1bv9qqj/file_qlik.rar?dl=0

in this folder the excel file "output.. " in this excel file the first table output which i need

Hubert Dudek · Answer 1 · 2019-07-09T16:55:44.977

0

Just add another if

=Sum(
    If(CalendarMonthStart >= [Start Date] and CalendarMonthEnd <= [End Date],
             CalendarMonthEnd-CalendarMonthStart,
        If([Start Date]>CalendarMonthStart,
            [Start Date]-CalendarMonthStart+1,
            CalendarMonthEnd-[End Date])
    )
 )
)

edited Jul 09 '19 at 16:55

answered Jul 06 '19 at 23:18

Hubert Dudek

1,666
1
13
21

this shows me wrong values... this is link of folder please download and check .. https://www.dropbox.com/s/v48373io1bv9qqj/file_qlik.rar?dl=0 .. in this folder the excel file "output.. " in this excel file the first table output which i need – MIRROR Jul 07 '19 at 03:00
hello @Hubert Dudek any solution please – MIRROR Jul 07 '19 at 13:39
I will try to look to it tomorrow – Hubert Dudek Jul 07 '19 at 22:50
I updated answer. In future you can learn how to create master calendar so it will be easier witch such a things. – Hubert Dudek Jul 09 '19 at 16:56
have my answer helped? – Hubert Dudek Jul 10 '19 at 17:52
at the second last record end date 31/Dec/2017 and calendar month end i get 1 day where it should be 31 days because he availed whole month – MIRROR Jul 11 '19 at 08:47

Days from dates in qlikview expression

1 Answers1