topic Re: Last 90 days count using Set Analysis in QlikView

Last 90 days count using Set Analysis

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

Hello All

I'm trying to create set analysis calculation like count of emails over last 90 days in qlikview

=count({$<{Mailing Time]={">$(=(Date(today()-90))"}>} %email)

but unfortunately date filter from isn't applied at all.

Mailing Time in database is timestamp field

if any more info needed wud be happy to share,thx.

Re: Last 90 days count using Set Analysis

oknotsen — Sat, 10 Jun 2017 09:24:08 GMT

Turn...

=count({$<{Mailing Time]={">$(=(Date(today()-90))"}>} %email)

... into...

=count({$<{[Mailing Time]={">$(=today()-90)"}>} %email)

... and see if that works.

What I did was...

- add a missing [ in front of your "Mailing Time" field.

- remove the date() function as that does formatting only, so has no added value here

- remove a bunch of useless brackets

Re: Last 90 days count using Set Analysis

its_anandrjs — Sat, 10 Jun 2017 09:24:59 GMT

You can make it date field

Ex:-

Load

date( [Mailing Time],'DD/MM/YYYY') AS [Mailing Date] //Add this new field for date field

From Source;

and use this field

=count({$<{[Mailing Date] = {">$(=(Date(today()-90))"}>} %email)

Regards,

Anand

Re: Last 90 days count using Set Analysis

sunny_talwar — Sat, 10 Jun 2017 10:05:38 GMT

What is the format of Mailing Time? The format plays a very important role in set analysis without which this type of set analysis won't really work.... So, here are some of your options

1) Use format of your Mailing Time field

=Count({$<[Mailing Time] = {">$(=TimeStamp(Today()-90, 'DD/MM/YYYY h:mm:ss TT'))"}>} %email)

Here I assumed that your Mailing Time was having a format like this DD/MM/YYYY h:mm:ss TT, but it was different, replace it with the correct format.

2) Use flag from the script

If([Mailing Time] > (Today() - 90), 1, 0) as Last90DayFlag

and then just this

=Count({$<Last90DayFlag = {1}>} %email)

3) Finally a search string method where you won't need to worry about the format

=Count({$<[Mailing Time] = {"=[Mailing Time] > (Today()-90)"}>} %email)

Re: Last 90 days count using Set Analysis

vadim_grab — Sun, 11 Jun 2017 14:54:05 GMT

Count({$<[Mailing Time] = {">=$(=Max(DateID)-90)<=$(=Max(DateID)"}, Year= , Month = }>} %email)