Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Count Function

Hi! Wondering how this is supposed to be formatted.

Count(distinct{$<[DATE MODIFIED] + EXPIRATION - [DATE MODIFIED.autoCalendar.Date] = {">3"}>} [ASSET TITLE])

This function doesn't work. Basically, I want to count the number of asset titles in which the Date modified plus the number of months in the expiration field minus today is greater than 3 months.

An asset's date modified is the last time it was touched. It's expiration is the amount of months it takes past when it was last touched for it to expire, which we can assume is 24 months right now. The last date modified field is today. I want to say, if an asset has been touched for 24 months (in this situation but the number will be different by each asset), count it. So, if the date it was touched + expiration (which gives you the date it will expire) is more than 3 months away from today, count it.

Please help!

1 Solution

Accepted Solutions

Re: Count Function

Just this

Sum(If(

Aggr([EXPIRATION] - [ASSET MODIFIED DATE.autoCalendar.MonthsAgo] , [ASSET TITLE]) >= 0 and

Aggr([EXPIRATION] - [ASSET MODIFIED DATE.autoCalendar.MonthsAgo] , [ASSET TITLE]) <= 3, 1, 0))

16 Replies

Re: Count Function

May be this

Count(DISTINCT{$<[ASSET TITLE] = {"=[DATE MODIFIED] + EXPIRATION - [DATE MODIFIED.autoCalendar.Date] > 3"}>} [ASSET TITLE])

Re: Count Function

One simple solution would be create field in script

Load DATE MODIFIED, EXPIRATION, DATE MODIFIED.autoCalendar.Date,

([DATE MODIFIED] + EXPIRATION) - [DATE MODIFIED.autoCalendar.Date] as Field3Combined;


Then

Count(distinct {< Field3Combined = {">3"}>} [ASSET TITLE])

Re: Count Function

Or for three months, you might need this (assuming 90 days in 3 months)

Count(DISTINCT{$<[ASSET TITLE] = {"=[DATE MODIFIED] + EXPIRATION - [DATE MODIFIED.autoCalendar.Date] > 90"}>} [ASSET TITLE])

pradosh_thakur
Valued Contributor III

Re: Count Function

Try this

please try altering syntax a bit if it fails


Count(DISTINCT{$<[ASSET TITLE] =

{"$(DATE(AddMonths([DATE MODIFIED],EXPIRATION),'DD/MM/YYYY') - date(today(),'DD/MM/YYYY')) > 90"}


>} [ASSET TITLE])


regards

Pradosh

Not applicable

Re: Count Function

I would like to do this. However, when I put it in the script, it has an error and says that the field "Date Modified" is not found?

Capture.PNG

I changed the function as well but not the formatting.

Not applicable

Re: Count Function

This doesn't recognize anything after " as a field?Capture.PNG

Re: Count Function

Don't like my ideas?

pradosh_thakur
Valued Contributor III

Re: Count Function

you didn't get any result?

Not applicable

Re: Count Function

You have helped me more than anyone! I have been trying out yours. I changed the function a bit so that its simply

Expiration - Date Modified Months Ago which gives me the result i need. So then I need a count of the asset titles in which Expiration - Months ago >3

Community Browser