Count Expression in Chart

[Anonymous]

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

[saimahasan]

You can use the expression as

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

[adamdavi3s]

Hi,

Hopefully this gives you the required answer?

[#4]

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]

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

[adamdavi3s]

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

[#7]

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]

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

[#9]

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?

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)