topic Set Analysis Count values over 20 days in QlikView

Set Analysis Count values over 20 days

Tue, 19 Jan 2016 08:25:13 GMT

Hi,

I'm hoping somebody can assist with the following expression:

=count({$<Skill={'1'}, NewOld={'Old'}, Role={'3'}, Active={'Y'}, Date={'=date(today()-20,'dd/mm/yyyy''}>})distinct(ID))

I'm trying to count the number of unique IDs where the latest date is over 20 days, how can I do this? the above formula that I tried doesn't work.

Best

Mav

sunny_talwar — Tue, 19 Jan 2016 11:13:45 GMT

May be this:

=Count({$<Skill={'1'}, NewOld={'Old'}, Role={'3'}, Active={'Y'}, Date={"$(='<=' & Date(Today() - 20, 'DD/MM/YYYY'))''}>} Distinct ID)

maxgro — Tue, 19 Jan 2016 11:18:02 GMT

=sum({<

Skill={'1'}, NewOld={'Old'}, Role={'3'}, Active={'Y'},

Date={"$(=date(today()-20, 'DD/MM/YYYY'))"}

>} distinct ID)

=sum({<

Skill={'1'}, NewOld={'Old'}, Role={'3'}, Active={'Y'},

Date={"<=$(=date(today()-20, 'DD/MM/YYYY'))"}

>} distinct ID)

Anonymous — Tue, 19 Jan 2016 11:18:45 GMT

may be this, some syntax missing?

=count({<Date=, Skill={'1'}, NewOld={'Old'}, Role={'3'}, Active={'Y'},

Date={'=$(=date(today()-20,'dd/mm/yyyy'))'} >} distinct(ID))

Make sure format of Date should be 'dd/mm/yyyy'

sunny_talwar — Tue, 19 Jan 2016 11:21:35 GMT

mm is for minutes Balraj. You will need to use MM here

jonathandienst — Tue, 19 Jan 2016 11:21:45 GMT

Looks OK to me,except that the date format is incorrect

date(today()-20,'dd/mm/yyyy''}

mm is minutes, you need MM in months

date(today()-20,'dd/MM/yyyy''}

I suggest that you test these sub expressions in a text box; That will help you debug the expression.