Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to count certain posts in a datefield?

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?

qw123.JPG

All suggestions and hints are welcome! Thanks

4 Replies
teempi
Partner - Creator II
Partner - Creator II

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

hic
Former Employee
Former Employee

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.

Not applicable
Author

Hi

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. 😃

hic
Former Employee
Former Employee

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 2nd parameter) and return the first value in the list (specified by the 3rd 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?