Set analysis help

[#1]

Hi all,

I have an if statement, which I'd like to convert into Set Analysis.

The expression is : count(distinct if(LAST_UPDATE_DATE_MAX=[Last Update Date],OrderNumber ))

I tried this is in Set analysis, but it doesn't return any thing.

=count(distinct {<LAST_UPDATE_DATE_MAX={"$(=[Last Update Date])"} OrderNumber))

I'd like to return if, it the one date field(Last Update Date Max) equals another date field (Last Update Date)

Any ideas as to what I'm doing wrong?

[Miguel_Angel_Baeyens]

Hi,

First, my guess is that in this case what you need is not set analysis rather a row by row comparison. Set analysis is evaluated once, and before doing the aggregations so the dataset is reduced to what set analysis specifies. In your formula, the left part is a field, that is OK, but the right part is a field as well, and my guess is that you want a value, no a lot of values.

If my assumtiom is correct, then you should use instead

= {'$(=Max([Last Update Date]))'}

Note that as mentioned this will compare the left part field to the value of that Max function for the whole chart, not row by row.

Hope that helps.

Miguel

[swuehl]

Try something like

=count(DISTINCT {<OrderNumber = {"=[Last Update Date]=LAST_UPDATE_DATE_MAX"}>} OrderNumber)

assuming that all three fields reside in the same table in your data model.

Instead of OrderNumber, you may need to use a field with the granularity required for your field value comparison (so that last update date and last update date max return an unambiguous answer).

[#4]

Hi,

try using  

count({<LAST_UPDATE_DATE_MAX={"$(=[Last Update Date])"} >} DISTINCT  OrderNumber)

Hope that helps.

mrunmayi

[#5]

Thanks for all of your answers.

I'm using it in a text object on my dashboard, but it returns a 0.

[#6]

If both are fields and may have, in a specific moment, more than 1 possible or selected value, try with this expression:

count({<LAST_UPDATE_DATE_MAX=p([Last Update Date]) >} DISTINCT  OrderNumber)

But I'm not 100% sure if the field in the left is that or the other one. Anyway, try it and see what happens .

Cheers,

Borja