We can, of course, present the list of dimensions using Concat:
=Concat(Customer, chr(10))
We can add a sort
=Concat(Customer, chr(10), -Aggr(Sum(LineValue),Customer))
If we tried to add the Sum into the Concat's first parameter, we would get an error about not allowing an aggregation.
We were racking our brains to come up with a way to add the value (without seeing the bleeding obvious! - see below). Eventually, I came up with this horrendous expression:
=If(Len(FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),1))>0,
FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),1)
& ' - ' & Num(Sum({
& chr(10), Null())
& If(Len(FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),2))>0,
FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),2)
& ' - ' & Num(Sum({
& chr(10), Null())
& If(Len(FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),3))>0,
FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),3)
& ' - ' & Num(Sum({
& chr(10), Null())
& If(Len(FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),4))>0,
FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),4)
& ' - ' & Num(Sum({
& chr(10), Null())
& If(Len(FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),5))>0,
FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),5)
& ' - ' & Num(Sum({
, Null())
=If(Len(FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),1))>0,
FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),1)
& ' - ' & Num(Sum({
& chr(10), Null())
& If(Len(FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),2))>0,
FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),2)
& ' - ' & Num(Sum({
& chr(10), Null())
& If(Len(FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),3))>0,
FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),3)
& ' - ' & Num(Sum({
& chr(10), Null())
& If(Len(FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),4))>0,
FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),4)
& ' - ' & Num(Sum({
& chr(10), Null())
& If(Len(FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),5))>0,
FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),5)
& ' - ' & Num(Sum({
& chr(10), Null())
& If(Len(FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),6))>0,
'Others'
& ' - ' & Num(Sum(LineValue)-Sum({
, Null())
But it doesn't show all the dimensions plus values - which was the original challenge.
A rethink was in order and I returned to the Concat with the sort. I wonder... If the sort expression accepts an Aggr, would that be allowed in the first parameter:
=Concat(Customer & ' - ' & Num(Aggr(Sum(LineValue), Customer), '#,##0'), chr(10), -Aggr(Sum(LineValue), Customer))
Yay! It worked!!!
Potentially, this could be used in text objects, alert texts, and popup texts.
Stephen Redmond is author of Mastering QlikView, QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond