Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Count Expression in Chart

I need help creating a COUNT expression with the attached data set.

Dimension: StartDate_YYYYMM

Expression:

AssignDate IS NOT NULL

Status = Open OR Status  = Closed when Enddate_YYYYMM = StartDate_YYYYMM

201608 = 50,213

201607 = 52,543

201606 = 45,026

24 Replies
saimahasan
Partner - Creator III
Partner - Creator III

You can use the expression as

=count(if(isnull(AssignDate)=0 and (Status='Open' or Status='Closed') and STARTDATE_YYYYMM=ENDDATE_YYYYMM,ID))

adamdavi3s
Master
Master

Hi,

Hopefully this gives you the required answer?

Not applicable
Author

i sugest create a flag on scrip and use on set analisys

flag script:

if(len(trim(AssignDate))>0 and ENDDATE_YYYYMM =STARTDATE_YYYYMM,1) as flag

expression:

=Count({< Status={"Open","Closed"},flag={1}>} STARTDATE_YYYYMM)

adamdavi3s
Master
Master

Does not properly account for Status = Open OR Status  = Closed when Enddate_YYYYMM = StartDate_YYYYMM

adamdavi3s
Master
Master

I agree that adding a flag is a good idea but your solution does not correctly account for Status = Open OR Status  = Closed when Enddate_YYYYMM = StartDate_YYYYMM

Flag should be

if(len(trim(AssignDate))>0 and ((ENDDATE_YYYYMM =STARTDATE_YYYYMM and status='Closed') OR (Status='Open')),1) as flag

Not applicable
Author

for this the for Status = Open OR Status  = Closed set analisys make

i think dependes of volum of data for not onarate the scrip a big if

Anonymous
Not applicable
Author

Thanks. I also need an accumulation of the numbers. Is that possible?

Not applicable
Author

ramgesum(above(your_expression),0,rowno())

sunny_talwar

Getting a slightly higher number, but seems to be close, can you check if there are more conditions or if your Excel count was not right?

Capture.PNG

Expression:

=RangeSum(Above(Count({<AssignDate={'*'}, Status={'Open'}> + <AssignDate = {'*'}, Status={'Closed'}, ID = {"=ENDDATE_YYYYMM = STARTDATE_YYYYMM"}>} ID), 0, RowNo()))

NOTE: Used the sample prepared by adamdavi3s‌ (Thanks)