How to Count on a dimension and not include the dimension in chart

[#1]

Hello,

I have an issue. I need to count number of occurrences and represent it in a pie chart. The issue i am facing is the count have to be by two fields which will not be included on the chart. I have similar data for multiple Group Id's and for each Group Id i need to show the results in one table.

For example: Below is by raw data: I need to count only the rows marked in red.

Group	ID	Result	Group ID	Date
Group 1	1539041	3	2	5/22/2013
Group 1	1539046	3	2	5/22/2013
Group 2	1539041	4	2	11/20/2013
Group 2	1539046	4	2	11/20/2013
Group 3	1539041	3	2	10/4/2012
Group 3	1539041	3.5	2	9/17/2013
Group 3	1539046	3	2	10/4/2012
Group 3	1539046	3.5	2	9/17/2013
Group 4	1539041	3	2	9/27/2012
Group 4	1539046	3.5	2	9/27/2012
Group 5	1539041	3.5	2	7/8/2012
Group 5	1539046	3	2	7/8/2012
Group 6	1539041	3.5	2	3/17/2014
Group 6	1539046	3	2	3/17/2014
Group 7	1539041	2.5	2	9/28/2012
Group 7	1539041	3	2	8/11/2013
Group 7	1539046	2.5	2	9/28/2012
Group 7	1539046	3	2	8/11/2013
Group 8	1539041	3.5	2	1/24/2014
Group 8	1539046	3.5	2	1/24/2014

The result i want is

Result	Count_result	Total_Count
3	7	16
3.5	7	16
4	2	16

Any help?

Thanks in advance.

[Anonymous]

I'm not seeing it: What determines whether a row should be counted or not?

[oknotsen]

Do you mean count the situations in which the combination of Result and Date is unique?

[#4]

Count the situation where the combination of maximum date and ID is unique for a group.

for example:

in Group 3, we only count two rows where Date is 9/17/2013 ( as this is maximum in Group 3)  for each ID

Group 3

1539041

3.5

2

9/17/2013

Group 3

1539046

3.5

2

9/17/2013

Hope this helps

[maxgro]

see attachment

[JonnyPoole · Accepted solution]

Here is a solution

Dimension:  Result

Count Rows Expression:

sum(aggr( if( Date = max( total <Group> Date) , 1, 0) , Result, Group,[Group ID],ID,Date))

Grand Total Expression:

if ( sum(aggr( if( Date = max( total <Group> Date) , 1, 0) , Result, Group,[Group ID],ID,Date)) > 0 ,

  sum( total aggr( if( Date = max( total <Group> Date) , 1, 0) , Result, Group,[Group ID],ID,Date))

)

[#7]

Thanks Massimo Grossi and Jonathan Poole. It worked.