topic How to count certain posts in a datefield? in QlikView

How to count certain posts in a datefield?

Fri, 13 Jan 2012 11:29:41 GMT

I hope someone can help me with a exprecssion for the count of just ceratin post in my datefield.

When I select month I get all post in the datekey that have something to do with this month. But how can I in a chart just show the ones that have a end date of the selected month? Sort of Count({'Datekey=*_2011-Current selectedMonth})%DateKey) and vice vers for started same month?

All suggestions and hints are welcome! Thanks

Re: How to count certain posts in a datefield?

teempi — Fri, 13 Jan 2012 11:59:29 GMT

Hey,

You could try creating a variable defined as follows:

vSearch = '"*_' & Only(Year) & '-' & Only(Month) & '-*"'

You need to figure out how to get the month in numeric format though Then in your chart you can use something like Count( {< Key = {$(vSearch)} >} Key).

A more efficient way would be to check in the script if the year-month matches the last part of the key field and flag rows that do. Then the expression would be something like Count( {< Flag = {1} >} Key). Make another similar flag for the first part if needed.

-Teemu

How to count certain posts in a datefield?

hic — Fri, 13 Jan 2012 12:07:03 GMT

There are several ways to do this using string functions. One way could be

Count(if(len(trim(subfield(%DateKey,'_',2)))>0,%DateKey))

where the subfield function extracts the string found after the underscore character.

How to count certain posts in a datefield?

Fri, 13 Jan 2012 12:39:48 GMT

Can you explain the expression Count(if(len(trim(subfield(%DateKey,'_',2)))>0,%DateKey)) a bit more Henric?

I´m new to QV and not´t that good at expressions. 😃

How to count certain posts in a datefield?

hic — Fri, 13 Jan 2012 12:58:40 GMT

The subfield function is used to pick out a substring from a field value that is in fact a list of values. So the subfield(%DateKey,'_',1) call will interpret the %DateKey as a list of field values separated by underscores (specified by the 2^nd parameter) and return the first value in the list (specified by the 3^rd parameter). Which is your start date.

Then you can use this information in different ways. You can for instance test if there is a start date at all:

if(len(trim(subfield(%DateKey,'_',1)))>0,...)

and you can (in a chart or text box) count the %DateKeys that lack a start date:

Count(if(len(trim(subfield(%DateKey,'_',1)))=0,%DateKey))

or you can count the %DateKeys that have end dates:

Count(if(len(trim(subfield(%DateKey,'_',2)))>0,%DateKey))

Does this make sense?