add new record to a dimension - aggr function

[#1]

Hi,

Please see attached qvw for a working example.

Problem: I have a field - Division - that has 5 different data values (div1 - div5). It is QV 101 to get a bar chart that would display today's and yesterday's sales broken by each division. However, I would like to add another bar - All - that would sum up all divisions and furhter display each divisions sales. I assume aggr would do it but I am a bit stuck. Getting this to work by defining a number of expressions is fine, however, not a desired approach.

Any help/suggestion is appreciated.

Thanks!

[#2]

Hi,

Maybe you can get a help from this sample.

Regards,

Janzen

[#3]

Janzen,

Had to change to helpful. Started playing further and it is not as straightforward as it seems. I forgot to mention i also have month selection. Please see update file. If I add All to the inline table, sum(total) does not work after month selection.

Thoughts?

Thanks!

[johnw · Accepted solution]

Instead of adding 'All' to the inline table, build a division group table, and use division group as the chart dimension.  Go back to using sum(today) and sum(yesterday) for the expressions.

DivisionGroups:

LOAD

text(fieldvalue('Division',recno())) as DivisionGroup

,text(fieldvalue('Division',recno())) as Division

AUTOGENERATE fieldvaluecount('Division')

;

CONCATENATE (DivisionGroups)

LOAD

'All' as DivisionGroup

,text(fieldvalue('Division',recno())) as Division

AUTOGENERATE fieldvaluecount('Division')

;

[#5]

Thanks John.

I solved the issue by:

1) creating a temp table where I loaded certain fields from e.g. table A

2) inner joined 'ALL' to the temp table

3) concatenated table A with values from the temp table

4) dropped the temp table

What you outlined is new to me and I need to spend some time to figure it out.

Thanks!

[johnw]

If the script is confusing, for now just worry about understanding the underlying idea, which is to create this new table:

DivisionGroups:
DivisionGroup  Division
All            Div1
All            Div2
All            Div3
All            Div4
All            Div5
Div1           Div1
Div2           Div2
Div3           Div3
Div4           Div4
Div5           Div5

If you use Division in a bar chart, it will behave normally.  If you instead use DivisionGroup, you'll get an additional bar that has the total.  A big advantage over what you ended up doing is that instead of each Division requiring two rows in the original (large) table, each only requires two rows in this new (tiny) table.  So it's far less data overall, and will load faster.  I would also expect it to perform better in charts, but I'm less certain about that, and only testing on a large data model would let you know for sure.

The script I wrote is a high-performance way to build this new table without resorting to hardcoding with an inline load.  The fieldvalue() function can grab all existing field values without referring to any underlying table, and so it doesn't have to loop through all the rows of your main data.  While this specific approach is secondary to the basic idea of the table above, I fairly routinely need to know all the values of a field in script, and this is a very fast way to handle that requirement, so it's something to get familiar with.

[#7]

John,

Thank you for your explanation! I played around with your suggestion in my original application and it is much more favourable than what I created. With your approach I am creating far less records in the original table and the end product (from the front end perspective) is the same.

Thanks!