6 Replies Latest reply: Oct 19, 2012 11:42 AM by Marko Banjanin RSS

    add new record to a dimension - aggr function

      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!

        • Re: add new record to a dimension - aggr function

          Hi,

           

          Maybe you can get a help from this sample.

           

          Regards,

           

          Janzen

            • Re: add new record to a dimension - aggr function

              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!

                • Re: add new record to a dimension - aggr function
                  John Witherspoon

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

                  ;

                    • Re: add new record to a dimension - aggr function

                      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!

                        • Re: add new record to a dimension - aggr function
                          John Witherspoon

                          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.