Set analysis

[#1]

Hi,

I have ID, Date and Flag in a table.

ID	Week	Flag	Row_No
1	w33	Y	1
1	w33	Y	2
1	w33	N	3
2	w33	Y	4
2	w34	Y	5
3	w34	Y	6

Now I have to count the IDs which have only Y status in the week selected. In this example, for w33, only ID-2 has to be counted.

I have tried to acheive this with set analysis count({<FLAG={'Y'},Row_No={"=max(Row_No)"}>}distinct ID). But it didn't work.

Any idea on this?

[swuehl]

Try

= -sum( aggr( FirstSortedValue(Flag, -Row_No) = 'Y', ID))

assuming the 'N' status flag will appear only on the highest Row_No per ID.

[whiteline]

Hi

You can try this one (use different quotes carefully as stated below):

=count({<FLAG={'Y'}, Row_No={"=max({<FLAG={'Y'}>} Row_No)"}>} distinct ID)