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

[ananyaghosh]

!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

[ananyaghosh]

hi,

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

[Nagaraju_KCS]

PFA,

[ananyaghosh]

Hi,

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

[ananyaghosh]

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

[swuehl]

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.

[Nagaraju_KCS]

PFA,

As  per your data WED having only two records,

These are the records,

1/17/2018

1/24/2018

[qliksus]

Maybe something like the below

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

[ananyaghosh]

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

[swuehl]

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')

[ananyaghosh]

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.