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

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?

• ###### Re: How to Count on a dimension and not include the dimension in chart

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

• ###### Re: How to Count on a dimension and not include the dimension in chart

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

• ###### Re: How to Count on a dimension and not include the dimension in chart

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

• ###### Re: Re: How to Count on a dimension and not include the dimension in chart

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))

)

• ###### Re: How to Count on a dimension and not include the dimension in chart

see attachment

• ###### Re: How to Count on a dimension and not include the dimension in chart

Thanks maxgro and jpe. It worked.