Expression Help

[#1]

I have a table -

Item     Value

ABC     120

ABC     80

ABC     0

DEF     -100

DEF     0

DEF     +100

GHI     0

GHI     0

GHI     0

I need to count of # of distinct items where the sum of the Value column is 0 (in this example it would be 2 - DEF and GHI).

Thanks,

Brett

[jerem1234]

Try something like:

count(distinct {<Item={'=sum(Value)=0'}>}Item)

Hope this helps!

[Anonymous]

Or maybe :

=sum( if (aggr( sum(Value) , [Item]) = 0 , 1 ) )

[#4]

Thanks Jeremiah and Bill.   Both seem to give the correct result when I limit to just one month of data.   When I build a chart with a YYYY-MM dimension the values are not the same.   How to I enhance these to re-calculate over each dimension change?

[#5]

I got it to work by including the Date in the aggr function too.

=sum(if(aggr(sum(), , ) = 0, 1)) / count(DISTINCT )

The table I’m working with has many different types of Transactions in it. I would like this formula to be enhanced to only include = ‘CYCLE COUNT’. This “if” statement would need to be on both side of the “/”. Possible?

Thanks again,

Brett

[#6]

I figured out ½ the If statement. I need help with where/how to place the if(='CYCLE COUNT', on the left side of the equation.

=sum(if(aggr(sum(), Site, , ) = 0, 1)) / count(DISTINCT if(='CYCLE COUNT',Site&'|'&[S Item]))

Thanks,

Brett

[jerem1234]

You can try:

sum(if(aggr(sum(), FIELD1, FIELD2) = 0 and FIELD3='CYCLE COUNT', 1)) / count(DISTINCT if(FIELD3='CYCLE COUNT', Site&'|'&[S Item]))

Hope this helps!

[#8]

The addition of “and =’CYCLE COUNT’” did impact the results but not in the same way as selecting it as a filter. Selecting as a filter gives the correct result. I have verified that the right side of the equation is returning the correct results with or without the filter selected so it is something on the left side.

Thanks again for your support –

Brett

[#9]

Figured it out.   {1<[Trans Type] = {'CYCLE COUNT'}>}  works good enough.   If the Trans Type is not selected only Cycle Count is utilized.  If something else happens to be selected "No Data" is returned... which is ok in this case.

Full expression ended up:

=sum(if(aggr(sum({1<[Trans Type] = {'CYCLE COUNT'}>} [Matl Qty]), Site, [S Item], [Matl Date YYYY-MM]) = 0, 1)) / count(DISTINCT if([Trans Type]='CYCLE COUNT',Site&'|'&[S Item]))

Thanks again for your help