Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am struggling putting together a summary table for the Base set of data below.
Year | Team | Count |
---|---|---|
2011 | A | 5 |
2011 | B | 6 |
2011 | C | 4 |
2011 | D | 9 |
2012 | A | 8 |
2012 | B | 2 |
2012 | C | 4 |
2012 | D | 5 |
I want the summary table to look like the one below.
Year | Team | Count |
---|---|---|
2011 | C | 4 |
2012 | B | 2 |
The table displays the Min count per year and the corresponding teams to that min count. The part i am struggling with is pulling though the corresponding teams. I have tried first sorted value but that doesn't seem to work I need a formula that pulls through the Team
Thanks for your help
Please see the attached QVW, does that help?
For me (if I understood you correctly) FirstSortedValue did work, but that function depends on the sorting in your loadscript.
BTW: What I found a bit strange in my small sample app is that I couldn't sort with an inline loadscript...? It gave me a "garbage collection after load..." error (therefore a resident load). Is that normal?
Hi
I am currently using the free version and i can't view any more files unfortunately. could you post the potential solution on the forum?
Regards
James
No problem of course:
load script:
base:
LOAD * INLINE [
Year, Team, Count
2011, A, 5
2011, B, 6
2011, C, 4
2011, D, 9
2012, A, 8
2012, B, 2
2012, C, 4
2012, D, 5
]
;
NoConcatenate
load * Resident base order by Count;
drop table base;
Expression for your chart
FirstSortedValue( Team,Count)
Dimension is Year
Let me know if it worked...
Can you provide a solution as an expression.
The count is likely to change based on different selections made on the solution. The suggestion you have made suggests the count remains static
James Makepeace
MI Analyst
Southern Rock Insurance
Tel: 01454635844
possible sugggestion.
to calculate the min count field i use this expression.
Min({$<[Insurer]-={'Asda OE', 'eCar'}>} [Premium])
is there another expression which works by saying return [Team] WHERE [Premium]=
Min({$<[Insurer]-={'Asda OE', 'eCar'}>} [Premium])
Hi miteamgib,
You have to select the minimum, use the group by clause and use also the left join.
See the attached.
Regards,
Zakaria Mourchid
Hi
i am using the feee version. i can't access your attachemnts
Regards
J
Hi
i am using the feee version. i can't access your attachemnts
Regards
J
His option is:
// ***** Data Source ***************************
Data:
LOAD * INLINE [
Year, Team, Count
2011, A, 5
2011, B, 6
2011, C, 4
2011, D, 9
2012, A, 8
2012, B, 2
2012, C, 4
2012, D, 5
];
/*
Sort:
load
Year,
Team,
max(Count)
Resident Data Group By Year, Team;
*/
Sort:
load
Year,
min(Count) as Count
Resident Data
group by Year;
left Join
LOAD
Year,
Count,
Team
Resident Data;
drop table Data;
But if I understand you correctly, the calculation of your count is derived from other fields, and that may be the complexity right? To solve that, I need more information on that I'm afraid...