Skip to main content
Marching toward a simplified navigation! READ ON
Showing results for 
Search instead for 
Did you mean: 

Set analysis with the dimension being ignored

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

1 Reply
Partner - Champion III
Partner - Champion III

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:

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

And then use the expression:

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


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein