topic Re: Qlikview - arrayList in QlikView

Qlikview - arrayList

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

i need to calculate difference between two date excluding sunday. I have table with dates and i need to calculate number of dates of repeated days from last date.

if i have dates like that

27-05-2017

29-05-2017

30-05-2017

I use this code in script

date(max(Date)) as dateMax,

date(min(Date)) as dateMin

And i get min date = 27-05-2017 and max date = 30-05-2017 then i use in expressions

=floor(((dateMax - dateMin)+1)/7)*6 + mod((dateMax - dateMin)+1,7)

+ if(Weekday(dateMin) + mod((dateMax - dateMin)+1,7) < 7, 0, -1)

And get result 3 days. Thats OK, but the problem is if I have next dates:

10-05-2017

11-05-2017

27-05-2017

29-05-2017

30-05-2017

When use previously code I get min date = 10-05-2017 and max date = 30-05-2017 and result 18, but this is not OK.

I need to count only dates from

27-05-2017

29-05-2017

30-05-2017

I need to get max date and go throw loop repeated dates and if have brake to see is that date sunday if yes then step that date and continue to count repeated dates and if i again have break and if not sunday than close loop and remember number of days.

In my case instead of 18 days i need to get 3 days.

Any idea?

Re: Qlikview - arrayList

el_aprendiz111 — Tue, 30 May 2017 16:47:46 GMT

sum(
if(
mod((dateMax - dateMin),7)
>=
if(
(weekday(dateMax)-6)<0,
weekday(dateMax)-6+7,
weekday(dateMax)-6)
,floor((dateMax - dateMin)/7)+1
,floor((dateMax - dateMin)/7))
)

Re: Qlikview - arrayList

Anonymous — Tue, 30 May 2017 20:34:30 GMT

I add this in expression and get for dates

10-05-2017

11-05-2017

27-05-2017

29-05-2017

30-05-2017

result 7 and I need to be 3. And for dates

27-05-2017

29-05-2017

30-05-2017

i get result 1???

Where I was wrong???

Re: Qlikview - arrayList

el_aprendiz111 — Tue, 30 May 2017 21:37:27 GMT

Hi,

Re: Qlikview - arrayList

Anonymous — Wed, 31 May 2017 06:24:53 GMT

Hi,

thanks on the help, but I don't need to count number of sundays.

I need to count number of days excluding sunday but i need to find max date then go throw loop to the min date.

While go to back to min date if I have break (do not have repeted date) i see is this missing date sunday if is not than take this date min date, and if it is sunday go to the nest break...

I need somthing like this