Last 90 days count using Set Analysis

[cognosbadri]

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.

[oknotsen]

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

[its_anandrjs]

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

[sunny_talwar]

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)

[vadim_grab]

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