Greater than and Less than - Set Analysis Expression using a Measure

[ca3868077]

Hello,

I'm trying to write a set analysis expression that counts the number of [Items] where the value of a measure [Fill %] is greater than 0 and less than 25.

I've written the formula several different ways, but no success, the results are incorrect:

Count({<[Fill %]={">0 <25"}>}Items)

Count({$<[Fill %] > {'0'}, [Fill %] < {'25'}>} Items)

Count({$<[Fill %] = {'>0'}, [Fill %] ={'<25'}>} Items)

None of the above work.  Any ideas or suggestions are greatly appreciated!

[Mark_Little]

Hi,

your first set analysis is what i would have expected. Have you tired just great than and less than on there own to see if they are returning values as expected.

[ca3868077]

Thank you for your response.  I have tried it with one at a time and no change.   In fact, no matter which version I use, it displays the same number as the result.

Could the issue be that my measure, [Fill %], is a measure and not a column field?

[vincent_ardiet_ · Accepted solution]

Yes you can't use a measure like this.
I guess this is a master item? 
In this case, this should be better :  Count({<Items={"=[Fill %]>0 And [Fill %]<25"}>}Items)
By the way if this is returning a percentage, 0.25 is maybe the upper boundary.

[sbaro_bd]

Hello @ca3868077 ,

Only the first one has a good synthax. It doesn't work because the Set analysis work like a filter on dimension, not on aggregated measure (on front-end part). Try an expression like and tell us if it works :

Count({<[Items]={"=Fill_calculation_expression > 0"}> * <[Items]={"=Fill_calculation_expression < 25"}>}Items)

Regards.

[ca3868077]

Thank you.  Your formula and syntax seem to produce a result that is possible.  Also, yes it is a master item measure and I've used 0.25 as the upper bounds.

I appreciate your help.  And thank you to everyone who responded with suggestions.

[Nixi80]

I have another question related to this problem.
An order with a "DUE DATE" connected to more Delivery Notes. 

I would like to count if the due_date is less than the max of delivery notes shipping date.

Thanks in advance

[BrunPierre]

@Nixi80 Try

=Count(DISTINCT If( [Due Date] < Aggr(Max(shipping date]), [Order#]),[Order#]))

[Nixi80]

The set Analysis function is this one:

=Count(DISTINCT If( ORDINATO.DATA< Aggr(Max(SHIPPED.DATA_BOLLA]), [ORDINATO.ESERCIZIO]&[ORDINATO.PROTOCOLLO]&[ORDINATO.NUMERO]&[ORDINATO.RIGA], 0)
,[ORDINATO.ESERCIZIO]&[ORDINATO.PROTOCOLLO]&[ORDINATO.NUMERO]&[ORDINATO.RIGA]))

And the result for each single row is 0.

I don't know if I'm making a mistake somewhere

[BrunPierre]

Maybe you're missing a bracket: Aggr(Max([SHIPPED.DATA_BOLLA]). Also, ensure the date is properly formatted, and the concatenated key may be inconsistent.