topic Re: Comparing one field with two variables in set analysis in QlikView

Comparing one field with two variables in set analysis

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

Hi, I'm having trouble with this formula:

COUNT({<SomeOtherDate={"$(='>=' & Min(Date) & '<=' & Max(Date)"}>} apples)

It filters Min(Date) correctly, but doesn't include Max(Date) in calculation as the comparison operator was '<'.

For example, formula COUNT({<SomeOtherDate={"$(='>=' '2020-09-01') & '<=' & '2020-09-30'"}>} apples)

would include dates 2020-09-01 to 2020-09-29. I want my 2020-09-30 in.

Can somebody help with this issue?

Re: Comparing one field with two variables in set analysis

Vegar — Wed, 07 Oct 2020 12:08:44 GMT

Are you sure that your datamodel hold data for SomeOtherDate = 2020-09-30? If so then make sure that the SomeOtherDate is in fact a date and note a timestamp that holds an decimal value as well?

You could try this expression expanding the interval to the end of the date '2020-09-30 23:59:59'.

COUNT({<SomeOtherDate={"$(='>=' & Min(Date) & '<=' & Max(dayend(Date))"}>} apples)

Re: Comparing one field with two variables in set analysis

edwin — Wed, 07 Oct 2020 12:16:23 GMT

try this:

COUNT({<SomeOtherDate={"$(=>2020-09-01<=2020-09-30"}>} apples)

COUNT({<SomeOtherDate={"$(=>$(=min(otherdate))<=$(=max(otherdate))"}>} apples)

Re: Comparing one field with two variables in set analysis

qliknonsense — Thu, 08 Oct 2020 05:38:56 GMT

Floor function solved the problem, thank you 🙂