Set analysis with the dimension being ignored

[samuel_brierley]

Hi all,

hopefully the expalnation of my data model and what im trying to acheive should frame my question sufficiently well. the expression that doesnt work is expression 2

here is my source table:

Fault_id, Created_date, actualCloseDate

Straight table properties

Dimension = Created_date

Expression 1 = count({1}fault_id) i.e how many faults where raised on a particular day

Expression 2 = count(total<Date>{1< actualCloseDate = p(Date)>} faultId) ****This is wrong

what i want from expression 2 is for the expression to look through the entire source table and count all of the  actualCloseDate that equal the Date in the dimension. 

thanks for any help

[jonathandienst]

It looks like you are trying to compare the dates on a row by row basis. You can't do that using set analysis as the set analysis filter is applied once to the whole data set before the table is constructed, not once per row. You could do this with a sum(if()) expression, like

count(if(Created_date = actualCloseDate, faultId))

That may perform poorly if your data set is large (many millions of rows). In that case, you need to create a derived flag field in the load script:

LOAD 
   ...
    if(Created_date = actualCloseDate, 1, 0) as Flag_Date,
   ...

And then use the expression:

count({<Flag_Date = {1}>} faultId))