topic Re: Date Range in Set Analysis not working in App Development

Date Range in Set Analysis not working

tauceef9 — Sat, 16 Nov 2024 08:33:52 GMT

Hello Everyone,

I have created 2 variables with below definition:

Previous FY Start = Num(if(month(today())>=4,date('04/01/'&(year(today())-1),'MM/DD/YYYY'),date('04/01/'&(year(today())-2),'MM/DD/YYYY')))

Previous FY End = Num(if(month(today())>=4,date('03/31/'&(year(today())),'MM/DD/YYYY'),date('03/31/'&(year(today())-1),'MM/DD/YYYY')))

both the variables are returning correct values (1st Apr 2016 & 31st Mar 2017) in number format, but when I am using these variables in my Set I am not getting expected output, below is my Set expression:

count({<DateNum={">=$(=Previous FY start)<=$(=Previous FY End)"},[Status]*={'Closed'}>}[ID])

When I hard code the number values what I am getting from these variables then above Set expression works fine.

Not sure where I am going wrong, please help me solving this issue.

Regards,

Tauceef

Re: Date Range in Set Analysis not working

jonathandienst — Wed, 14 Mar 2018 06:04:32 GMT

Try these

count({<DateNum = {">=$(Previous FY start) <=$(Previous FY End)"}, [Status] *= {'Closed'}>} ID)

count({<DateNum = {">=$(=Date(Previous FY start)) <=$(=Date(Previous FY End))"}, [Status] *= {'Closed'}>} ID)

(assumes DateNum is in the default date format)

Re: Date Range in Set Analysis not working

tauceef9 — Wed, 14 Mar 2018 06:41:36 GMT

Hi Jonathan,

Thanks for your quick reply,

1st Expression is returning 0 to me and 2nd one is returning same values which I am getting from the Set I have shared above.

with respect to DateNum values, these are correct only because as I have mentioned above, I am getting correct result when I hard code the numbers instead of Start date and End date variables.

Edited:

Just to add more if that helps: that filter is not getting applied i.e I am getting overall data as if no filter is applied, but when I replace variables with numbers it works fine.

Regards,

Tauceef

Re: Date Range in Set Analysis not working

sunny_talwar — Wed, 14 Mar 2018 11:27:30 GMT

What about this

Count({<DateNum = {">=$(=$(Previous FY start))<=$(=$(Previous FY End))"}, [Status] *= {'Closed'}>} [ID])

Re: Date Range in Set Analysis not working

tauceef9 — Thu, 15 Mar 2018 07:07:19 GMT

Hi Sunny,

Thanks a lot man, it is working.

I actually changed my code yesterday to:

count({<[Status]*={'Closed'}>} if(DateNum>=$(Previous FY start) and DateNum<=$(Previous FY End),[ID]))

which worked for me, but your code is also working.

Would you be able to explain why it is like that(including extra $()) because I have other variables as well which are working fine with the kind of code I have shared in my original post.

Thanks again.

Regards,

Tauceef

Re: Date Range in Set Analysis not working

sunny_talwar — Thu, 15 Mar 2018 08:52:39 GMT

It all depends how your variable is defined.... I have found it difficult to explain when is it needed and when is it not needed...but these threads might help

The Magic of Dollar Expansions

The Magic of Variables

The Little Equals Sign

Re: Date Range in Set Analysis not working

Samrithi — Wed, 12 Jun 2019 22:16:15 GMT