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

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!

16 Replies
Not applicable
Author

No, it is a count of 0

pradosh_thakur
Master II
Master II

hi stalwar1

can you please point out the error in my expression ?

regards

Pradosh

Learning never stops.
Anil_Babu_Samineni

There is small mistake, That may be the cause. Can you trouble shoot to use " " instead of [ ]

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

Capture 1.PNGCapture.PNG

Not applicable
Author

Thank you everyone.

The correct answer I found is

Sum (if( Aggr( [EXPIRATION] - [ASSET MODIFIED DATE.autoCalendar.MonthsAgo] , [ASSET TITLE]  )  >= 6 , 1, 0 ))

Which is borrowed from a previous question's answer. I am now having trouble adapting this for the values that fall between numbers. So now, I need a count of those between 0 and 3. Can't get the formatting right.

sunny_talwar

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))

Not applicable
Author

You rock! Thank you.