Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You can use the expression as
=count(if(isnull(AssignDate)=0 and (Status='Open' or Status='Closed') and STARTDATE_YYYYMM=ENDDATE_YYYYMM,ID))
Hi,
Hopefully this gives you the required answer?
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)
Does not properly account for Status = Open OR Status = Closed when Enddate_YYYYMM = StartDate_YYYYMM
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
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
Thanks. I also need an accumulation of the numbers. Is that possible?
ramgesum(above(your_expression),0,rowno())
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)