Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
)
I'm not seeing it: What determines whether a row should be counted or not?
Do you mean count the situations in which the combination of Result and Date is unique?
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
see attachment
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))
)
Thanks Massimo Grossi and Jonathan Poole. It worked.