topic Re: Set Analysis Date Range in QlikView

Set Analysis Date Range

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

sum(

Country -= {'Korea', 'Latvia', 'Northern Ireland'},

[Weekly Control Description] -= {'Avaya'},

Fiscal_Year_Week ={">=$(=replace(Weekname(Today()-77), '/', '-'))"}

This works fine and gives me my result.

However, I want to do a a range of 77 days (11 weeks) and 35 days (5 weeks). The minute I put in the greater than symbol, I get 0

sum(

Country -= {'Korea', 'Latvia', 'Northern Ireland'},

[Weekly Control Description] -= {'Avaya'},

Fiscal_Year_Week ={">=$(=replace(Weekname(Today()-77), '/', '-')) <=$(=replace(Weekname(Today()-77), '/', '-'))"}

Any help appreciated

Re: Set Analysis Date Range

Clever_Anjos — Thu, 19 Oct 2017 13:37:41 GMT

Please try changing (adding/removing) spaces around <=

Meantime, please add this expression to a straight table expression with no header.

Mouse over the header and check how your expression is beeing evaluated

Re: Set Analysis Date Range

Anil_Babu_Samineni — Thu, 19 Oct 2017 13:44:41 GMT

This works fine and gives me my result.

However, I want to do a a range of 77 days (11 weeks) and 35 days (5 weeks). The minute I put in the greater than symbol, I get 0

sum(

{<

Country -= {'Korea', 'Latvia', 'Northern Ireland'},

[Weekly Control Description] -= {'Avaya'},

Fiscal_Year_Week ={">=$(=replace(Weekname(Today()-77), '/', '-')) <=$(=replace(Weekname(Today()-77), '/', '-'))"}

>}

Any help appreciated

Looks like, >= and <= are same conditions. Are you going to do this? >= 2017-30 and <= 2017-30 ??

Re: Set Analysis Date Range

bobbydave — Thu, 19 Oct 2017 14:34:26 GMT

That is a typo

sum(

Country -= {'Korea', 'Latvia', 'Northern Ireland'},

[Weekly Control Description] -= {'Avaya'},

Fiscal_Year_Week ={">=$(=replace(Weekname(Today()-77), '/', '-')) <=$(=replace(Weekname(Today()-35), '/', '-'))"}

Still doesnt work

Re: Set Analysis Date Range

bobbydave — Thu, 19 Oct 2017 14:36:13 GMT

I ended up creating a a number of set analysis for each week and got the result.

Set analysis for 77 days

set analysis for 70 days

set analysis for 35 days

and added these all together to get my result. Long winded but works

Re: Set Analysis Date Range

bobbydave — Thu, 19 Oct 2017 14:37:48 GMT

>=2017-37<=2017-30

That was a typo in my original script

Re: Set Analysis Date Range

lorenzoconforti — Thu, 19 Oct 2017 21:47:14 GMT

Do you mind sharing your application? Maybe we can find a better way of achieving the expected result

There was another user having a similar issue a couple of days ago but I don't think it got resolved as the application couldn't be shared

Re: Set Analysis Date Range

bobbydave — Fri, 20 Oct 2017 07:17:28 GMT

I was able to do it another way for individual weeks to get the desired result. I was trying to do it using a date range which i couldnt figure out. Here is how i got around it.

= (sum(

Country -= {'Korea', 'Latvia', 'Northern Ireland'},

[Weekly Control Description] -= {'Avaya'},

Fiscal_Year_Week ={"$(=replace(Weekname(Today()-77), '/', '-'))"}

[Weekly Record Count])

sum(

Country -= {'Korea', 'Latvia', 'Northern Ireland'},

[Weekly Control Description] -= {'Avaya'},

Fiscal_Year_Week ={"$(=replace(Weekname(Today()-70), '/', '-'))"}

[Weekly Record Count])

sum(

Country -= {'Korea', 'Latvia', 'Northern Ireland'},

[Weekly Control Description] -= {'Avaya'},

Fiscal_Year_Week ={"$(=replace(Weekname(Today()-63), '/', '-'))"}

[Weekly Record Count])

sum(

Country -= {'Korea', 'Latvia', 'Northern Ireland'},

[Weekly Control Description] -= {'Avaya'},

Fiscal_Year_Week ={"$(=replace(Weekname(Today()-56), '/', '-'))"}

[Weekly Record Count])

sum(

Country -= {'Korea', 'Latvia', 'Northern Ireland'},

[Weekly Control Description] -= {'Avaya'},

Fiscal_Year_Week ={"$(=replace(Weekname(Today()-49), '/', '-'))"}

[Weekly Record Count])

sum(

Country -= {'Korea', 'Latvia', 'Northern Ireland'},

[Weekly Control Description] -= {'Avaya'},

Fiscal_Year_Week ={"$(=replace(Weekname(Today()-42), '/', '-'))"}

[Weekly Record Count])

sum(

Country -= {'Korea', 'Latvia', 'Northern Ireland'},

[Weekly Control Description] -= {'Avaya'},

Fiscal_Year_Week ={"$(=replace(Weekname(Today()-35), '/', '-'))"}

[Weekly Record Count]))

Re: Set Analysis Date Range

Clever_Anjos — Fri, 20 Oct 2017 09:57:59 GMT

Did you do this

"Meantime, please add this expression to a straight table expression with no header.

Mouse over the header and check how your expression is beeing evaluated"

Re: Set Analysis Date Range

Anil_Babu_Samineni — Wed, 25 Oct 2017 07:38:11 GMT

Please check validation in Text boxes ..