topic Re: Expression Help in QlikView

Expression Help

Tue, 20 Jan 2015 18:44:09 GMT

I have a table -

Item Value

ABC 120

ABC 80

ABC 0

DEF -100

DEF 0

DEF +100

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

Re: Expression Help

jerem1234 — Tue, 20 Jan 2015 18:48:06 GMT

Try something like:

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

Hope this helps!

Re: Expression Help

Anonymous — Tue, 20 Jan 2015 18:55:26 GMT

Or maybe :

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

Re: Expression Help

Tue, 20 Jan 2015 19:11:41 GMT

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?

Re: Expression Help

Tue, 20 Jan 2015 21:19:15 GMT

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

Re: Expression Help

Tue, 20 Jan 2015 22:30:39 GMT

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

Re: Expression Help

jerem1234 — Wed, 21 Jan 2015 13:57:44 GMT

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!

Re: Expression Help

Wed, 21 Jan 2015 15:35:40 GMT

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

Re: Expression Help

Wed, 21 Jan 2015 22:00:15 GMT

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