topic Re: How can I calculate the number of wednesday exists in a date range? in QlikView

How can I calculate the number of wednesday exists in a date range?

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

!Hi,

I have created a date filed and assign the value to a variable vDate and I have a date range also and assign the value to a variable vRANGE. Now if the use select vDate as 24/1/2018 and range as 9, the I have to calculate the number of Wednesday which is actually the creation date in that range.

SO if vDate is 24/1/2018 and vRange is 9, the the date range is 15/1/2018 To 24/1/2018 and I need to calculate how many weekday means Sunday, Monday, Tuesday, Wednesday, Thrusday, Friday and Saturday is in between in that date range?

I have used the below set analysis for this purpose:

count({$<CREATION_DATE={">$(=(Date(Date#('$(vDATE)','M/DD/YYYY')-if('$(vRANGE)'='0','1','$(vRANGE)'))))<=$(vDATE)"}>}weekday(CREATION_DATE))

but it gives me nothing.

so any idea will be appreciated.

Thanks,

Sandip Ghosh

Re: How can I calculate the number of wednesday exists in a date range?

YoussefBelloum — Fri, 09 Mar 2018 14:11:09 GMT

Hi,

use this set analysis part:

your_date_field={"=weekday(your_date_field)=2"}

Re: How can I calculate the number of wednesday exists in a date range?

ananyaghosh — Fri, 09 Mar 2018 14:14:06 GMT

I can't understand your point. Please clarify it.

Re: How can I calculate the number of wednesday exists in a date range?

YoussefBelloum — Fri, 09 Mar 2018 14:36:56 GMT

on the title of your thread it is said that you want to: calculate the number of Wednesday that exist on range date.

my suggestion is to add this to you expression:

your_date_field={"=weekday(your_date_field)=2"} ==> to calculate the number of Wednesdays on your Date range.

Re: How can I calculate the number of wednesday exists in a date range?

Peter_Cammaert — Fri, 09 Mar 2018 14:55:13 GMT

If want to know how many times the weekday of vDate occurs in a preceding period of vRange days and including vDate itself, then that number can be calculated simply as:

=floor(vRange/7)+1

e.g. vDate=24/1/2018 and vRange=9 means that between 15/1/2018 and 24/1/2018 there are floor(9/7)+1 = 2 Wednesdays.

BTW this works in both directions.

Re: How can I calculate the number of wednesday exists in a date range?

ananyaghosh — Fri, 09 Mar 2018 15:04:53 GMT

Hi,

YOur calculations works for me, so what will be the calculation for

1. Saturday

2. Sunday,

3. Monday

4.Tuesday

5. thursday

and Friday also?

Please help me in those case also. I am looking forward your reply.

Re: How can I calculate the number of wednesday exists in a date range?

ananyaghosh — Mon, 12 Mar 2018 07:55:09 GMT

Hi,

Please help for this purpose as I am looking forward to your reply.

Thanks,

Sandip Ghosh

Re: How can I calculate the number of wednesday exists in a date range?

Nagaraju_KCS — Mon, 12 Mar 2018 08:22:02 GMT

Try this Expression

=count({1<CalendarWeek={'Wed'},CalendarDate={">=$(=Date#('01-01-2000','DD/MM/YYYY'))<=$(=Date#('31/01/2000','DD-MM-YYYY'))"}>}CalendarDate).

Re: How can I calculate the number of wednesday exists in a date range?

ananyaghosh — Mon, 12 Mar 2018 09:52:11 GMT

Hi,

I am using the below set expression:

=count({$<Week_Day={'Wed'},

CREATION_DATE={">$(=(Date(Date#('$(vDATE)','M/DD/YYYY')-if('$(vRANGE)'='0','1','$(vRANGE)'))))<=$(=Date#('$(vDATE)','M/DD/YYYY'))"}>}CREATION_DATE)

where I have to count the the number of Wednesday in a specific date range and vDATE is the variable name where I have put the date value and vRANGE is the date range which is actually is a numeric number.

But this is not working for me. Please help me in this case.

Re: How can I calculate the number of wednesday exists in a date range?

Nagaraju_KCS — Mon, 12 Mar 2018 10:30:54 GMT

Try this,

=count({<Week_day={'Wed'}, [Last Login Time]={">=$(=date($(vMinLastlogin)))<=$(=date($(vMaxLastlogin)))"}>}DISTINCT [Last Login Time])

Change Your variables..

Hope this works...!

Re: How can I calculate the number of wednesday exists in a date range?

ananyaghosh — Mon, 12 Mar 2018 10:35:08 GMT

hi,

Can u Give me your sample qvw or qvf files file so that I can compare it with my application?

Re: How can I calculate the number of wednesday exists in a date range?

Nagaraju_KCS — Mon, 12 Mar 2018 10:53:39 GMT

PFA,

Re: How can I calculate the number of wednesday exists in a date range?

ananyaghosh — Mon, 12 Mar 2018 13:36:33 GMT

Hi,

I have made my sample QVW FILE AND aTTACHED IT. But it does not work for me. Please...Please help me. !

Re: How can I calculate the number of wednesday exists in a date range?

ananyaghosh — Mon, 12 Mar 2018 14:14:43 GMT

Please correct me by checking the Apps . I am waiting for your reply.

Re: How can I calculate the number of wednesday exists in a date range?

swuehl — Mon, 12 Mar 2018 14:28:02 GMT

What do you mean with 'it doesn't work for me' exactly?

Your sample file uses two dates to define your range instead of a start date and a range number.

Besides this, you are counting field values. You can only count a Wednesday date if that date is part of your field values. Seems like your data misses some dates.

You can use a master calendar to get around this or use a formula that does not work on your model, for example something like Peter suggested.

Re: How can I calculate the number of wednesday exists in a date range?

Nagaraju_KCS — Mon, 12 Mar 2018 14:46:02 GMT

PFA,

As per your data WED having only two records,

These are the records,

1/17/2018

1/24/2018

Re: How can I calculate the number of wednesday exists in a date range?

qliksus — Tue, 13 Mar 2018 04:16:41 GMT

Maybe something like the below

substringcount ( concat(distinct (weekday(date(ValueLoop(num(Today()),num(Today())+9,1)))) ,',')),'Wed')

Re: How can I calculate the number of wednesday exists in a date range?

ananyaghosh — Tue, 13 Mar 2018 10:15:02 GMT

I have used your code and it gives me error. Please can you correct it?

Re: How can I calculate the number of wednesday exists in a date range?

swuehl — Tue, 13 Mar 2018 10:35:46 GMT

There is a closing parenthesis too much. Remove it. And remove the DISTINCT clause from the Concat() function.

=SubstringCount(concat( (weekday(date(ValueLoop(num(Today()),num(Today())+9,1)))) ,','),'Wed')

Re: How can I calculate the number of wednesday exists in a date range?

ananyaghosh — Tue, 13 Mar 2018 10:56:22 GMT

so according to your code wednesday count is showing as 2, but if you count the it it should show only 1(3/7/2018) as because the date range is 3/13/2018 To 3/5/2018. Please clarify me if I am wrong.

Please respond as I am waiting for your reply.