topic Re: ¿How to count Saturdays in date range? in QlikView

¿How to count Saturdays in date range?

Fri, 06 May 2016 14:16:27 GMT

Hi there,

I need your help to solve my issue, i need to count saturdays in date range:

I have a field with date (Format: DD/MM/YYYY hh:mm:ss) and i make filters in diferents range but i need only count how many saturdays exist into this range selected (the range is variable).

Thanks for all!.

Thanks in advance.

Re: ¿How to count Saturdays in date range?

swuehl — Fri, 06 May 2016 14:22:01 GMT

Maybe like shown here

Re: Count of Saturday in whole month

=Div(WEEKDAY(Min(DateField)-6)+1-Min(DateField)+Max(DateField),7)

Re: ¿How to count Saturdays in date range?

florentina_doga — Fri, 06 May 2016 14:29:18 GMT

in script;

aa:

load *,

WeekDay(data) as week_name;

load * inline [data

1/1/2016

2/1/2016

3/1/2016

4/1/2016];

and in interface

count(distinct if(week_name='Sat',data))

Re: ¿How to count Saturdays in date range?

Fri, 06 May 2016 14:32:26 GMT

Does not work!

Re: ¿How to count Saturdays in date range?

MK_QSL — Fri, 06 May 2016 14:38:34 GMT

Div(Date2-Date1,7) + (WeekDay(Date1)<=5)*(WeekDay(Date2)>=5)

Re: ¿How to count Saturdays in date range?

swuehl — Fri, 06 May 2016 14:41:46 GMT

'Does not work' is not a really helpful issue description.

Do you get an error, no value, wrong value?

(I assume you have replaced DateField with the field name of your date field).

See attached a sample QVW.

Re: ¿How to count Saturdays in date range?

florentina_doga — Fri, 06 May 2016 14:43:24 GMT

see this attach

Re: ¿How to count Saturdays in date range?

Fri, 06 May 2016 14:51:48 GMT

Does not work, this formule is counting 1 with any saturdays selected.

Re: ¿How to count Saturdays in date range?

tamilarasu — Fri, 06 May 2016 14:57:30 GMT

Maybe try,

Count(If(Weekday(DateField)='Sat',1))

Re: ¿How to count Saturdays in date range?

smirkinaa — Fri, 06 May 2016 14:57:49 GMT

Hi, I suggest to use additional field with number of weekday, which we have to add in the load script. And than you can use expression like this "Count({<WeekDayField = {5}>} DISTINCT DateField)". We have to use integer values in DateField. That's why we should use transformation for it: Floor(BaseDateField).

Re: ¿How to count Saturdays in date range?

swuehl — Fri, 06 May 2016 14:58:58 GMT

Not sure what your list boxes are showing. Have you selected a single week in the middle list box?

Re: ¿How to count Saturdays in date range?

Fri, 06 May 2016 15:05:15 GMT

Yes, the green color is current selection. I select Week 17 and 18,19,20,21 and 22 of April.

Re: ¿How to count Saturdays in date range?

swuehl — Fri, 06 May 2016 15:16:55 GMT

Well, could you upload your current sample file?

edit:

Are you using a different week start / week numbering compared to ISO?

Re: ¿How to count Saturdays in date range?

Fri, 06 May 2016 15:24:47 GMT

The file is uploaded in the main post.

Re: ¿How to count Saturdays in date range?

swuehl — Fri, 06 May 2016 15:25:59 GMT

Can't see an attached file.

Re: ¿How to count Saturdays in date range?

Fri, 06 May 2016 15:27:30 GMT

Check again, pls.

Re: ¿How to count Saturdays in date range?

swuehl — Fri, 06 May 2016 15:55:31 GMT

You have changed the system variable FirstWeekDay, which influences Weekday() function.

You can correct this like

Div(WEEKDAY(Min([Creation Time])-6,0)+1-Min([Creation Time])+Max([Creation Time]),7)

Re: ¿How to count Saturdays in date range?

Fri, 06 May 2016 16:20:55 GMT

Thanks you!

The problem its solved.