Count over several rows

[jessica_webb]

Hi,

I have a rather large table with the following dimensions:

ID	Group	Year	Loc	LocCount	Status
123	R	2012	ABC	3	A
123	R	2013	ABC	5	A
123	R	2014	ABC	2	A
123	R	2015	ABC	3	A
456	1	2012	ABC	1	C
456	1	2013	ABC	-	C
456	1	2014	ABC	3	C
456	1	2015	ABC	2	C
789	R	2012	DEF	-	D
789	R	2013	DEF	6	D
789	R	2014	DEF	1	D
789	R	2015	DEF	2	D
789	R	2012	GHI	1	E
789	R	2013	GHI	1	E
789	R	2014	GHI	-	E
789	R	2015	GHI	-	E

From this, I want an expression for the 'Status' column using the following criteria (in the hierarchical order shown below):

A - If the LocCount for the ID, Group and Loc is at least 2 in every year

B - If the LocCount for the ID, Group and Loc is at least 1 in every year

C - If the LocCount for the ID, Group and Loc is at least 2 in 2015 and 2014

D - If the LocCount for the ID, Group and Loc is at least 1 in 2015 and 2014

E - If above criteria not met for any ID, Group and Loc

I've completed the status column in the example above with what I would expect to see.

What's the best way to achieve this? In the script or by expression only?

Help would be really appreciated on this as I'm on a very tight schedule and not sure where to start!!

[YoussefBelloum]

Sun is shining

thank you Sunny !

[jessica_webb]

Hi Sunny,

Thank you as always!!!

It seems to work perfectly, but I don't really understand why! Are you able to explain a bit?

If(Min(TOTAL <ID, Group, Loc> Alt(LocCount, 0)) = 2, 'A',

This seems to mean that it should return 'A' if the minimum value for that ID, Group, Loc combo is 2. Which obviously isn't right. So where in the code is it specifying that this should be for every year?

[sunny_talwar]

You have two main conditions

1) Min(TOTAL <ID, Group, Loc> Alt(LocCount, 0))

2) Min(TOTAL <ID, Group, Loc> {<Year = {2015, 2014}>} Alt(LocCount, 0))

Create them as expression in your chart and see what happens here

Shows the minimum value based on ID, Group, Loc for all the rows grouped by ID, Group, Loc

[jessica_webb]

Hi Sunny,

I've hit upon an issue I hadn't thought of that gives me incorrect results:

ID	Group	Year	Loc	LocCount	Status
64571	10	2014	AB	1	3
64571	10	2014	BC	1	3
64571	10	2014	CD	1	3
64571	10	2014	-	5	-
64571	10	2015	DE	1	3
64571	10	2015	EF	1	3
64571	10	2015	FG	1	3
64571	10	2015	GH	1	3
64571	10	2015	HI	1	3
64571	10	2015	IJ	1	3
64571	10	2015	JK	1	3
64571	10	2015	-	1	-
64571	10	2016	KL	1	3
64571	10	2016	LM	1	3
64571	10	2016	FG	1	3
64571	10	2016	MN	1	3
64571	10	2016	NO	1	3
64571	10	2016	OP	1	3
64571	10	2016	PQ	1	3
64571	10	2016	QR	1	3
64571	10	2016	RS	1	3
64571	10	2016	ST	1	3
64571	10	2016	-	1	-

If a 'Loc' doesn't have a 'LocCount' in previous years, it doesn't show in the data. Which means each value appears to have appeared once in every year. But every year should in fact be checkign that it appears in 2013, 2014, 2015, 2016… Any ideas?

[sunny_talwar]

Are you talking about the places where you have Null or '-' Loc? Is that where the problem is?

[jessica_webb]

The table I've just posted should have 'E' as the status for all, except 'FG' as they don't have a LocCount for every year - every year being 2014, 2015 and 2016 . However, they are coming up as status 'B' (sorry for confusion in table, I swapped the statuses from letters to numbers in my code). - because the code is looking for LocCount in every year that they are listed in.

Does that make sense??

[sunny_talwar]

Let's take a step back and put down for me the conditions in words again....

[sunny_talwar]

Why is "FG" B.... FG is not available for all the years also? FG is only available in 2015 and 2016? Should it not be E also in that case?

[jessica_webb]

A - If the LocCount for the ID, Group and Loc is at least 2 in every year

B - If the LocCount for the ID, Group and Loc is at least 1 in every year

C - If the LocCount for the ID, Group and Loc is at least 2 in 2016 and 2015

D - If the LocCount for the ID, Group and Loc is at least 1 in 2016 and 2015

E - If above criteria not met for any ID, Group and Loc

I've had to change the years - so apologies for causing a bit of confusion there.

So with the years changed in the criteria above, FG should be showing as 'B', but the rest should all be 'E'.

[sunny_talwar]

For FG to be 'B', it needs to have at least 1 in every year? Meaning all the three years (2014, 2015, 2016), right? By FG is only available in 2015 and 2016