topic Re: Count If Greater Than Date in QlikView

Count If Greater Than Date

Sun, 22 Feb 2015 17:43:30 GMT

Hi There

I want to be able to count the numbert of items that I have in stock which were purchased >= number of days ago.

In the load script I have created 5 variables;

Let v7DaysAgo = Today()-7;

Let v30DaysAgo = Today()-30;

Let v90DaysAgo = Today()-90;

Let v180DaysAgo = Today()-180;

Let v365DaysAgo = Today()-365;

So if I want to count the number of items that I have which were purchased, say >=180 days ago, I was expecting this expression to work;

=Count({$<[Purchased Date]={'>=v180DaysAgo'}>[ItemID])

...but this is returning an error. I have tried searching for an answer but with no luck so far. Any help appreciated!

Re: Count If Greater Than Date

Anonymous — Sun, 22 Feb 2015 18:18:10 GMT

Try doing you variables like this :

Let v7DaysAgo = date(Today()-7);

And your expressions like this :

=Count({$<[Purchased Date]={">=$(v7DaysAgo)"} >} [ItemID])

Re: Count If Greater Than Date

MK_QSL — Sun, 22 Feb 2015 18:27:40 GMT

=COUNT({<[Purchased Date] = {'>=$(=Date(v180DaysAgo))'}>}ItemID)

Re: Count If Greater Than Date

sunny_talwar — Sun, 22 Feb 2015 19:14:55 GMT

This should work:

Let v7DaysAgo = Date(Today()-7);

Let v30DaysAgo = Date(Today()-30);

Let v90DaysAgo = Date(Today()-90);

Let v180DaysAgo = Date(Today()-180);

Let v365DaysAgo = Date(Today()-365);

and then use the expression as follows:

=Count({<[Purchased Date] = {'>=$(v180DaysAgo)'}>} [ItemID])

You just need to make sure that [Purchase Date] format is the same as the format of your variable v180DatsAgo.

HTH

Best,

Re: Count If Greater Than Date

Anonymous — Sun, 22 Feb 2015 19:33:40 GMT

Hi,

Try like this :

Define Variable:

Let v180DaysAgo = date(Today()-180);

expressions in ui:

=Count({$<[Purchased Date]={">=$(v180DaysAgo)"} >} [ItemID])

Regards

Neetha

Re: Count If Greater Than Date

maxgro — Sun, 22 Feb 2015 19:41:57 GMT

this

=COUNT({<[Purchased Date]={">=$(=date(v180DaysAgo))"}>} ItemID)

or this

=COUNT({<[Purchased Date]={">=$(v180DaysAgo)"}>} ItemID)

depending on Purchase Date format