4 Replies Latest reply: Aug 8, 2011 11:19 AM by Anand Chouhan RSS

    Value (Expression) Field Groups

      Hi,

       

      I have a data table (cross table) with a large number of rows and multiple value fields per record/row. e.g.

       

      Company,Account,Owner, Balance, Validated Balance, Debit Unvalidated Balance, CR Unvalidated Balance, Net Unvalidated Balance, P&L DR, P&L CR, P&L

      1234, 123456789, PersonA, 1000, 500, 800,-300, 500, 15, -10, 5

       

      I am trying to present this in a pivot table with:

       

      • Multiple Expressions for each value based on simple sums and counts/% - no problem with this
      • Headings to group the expressions into logical groups e.g. Balances (all metrics based on Balance), Unvalidated (all metrics using the unvalidated value fields), PL

       

      BalanceUnvalidated BalancesP&L
      CompanyOwnerBalance% of Total BalanceDebit Unvalidated BalanceCR Unvalidated BalanceNet Unvalidated BalanceP&L DRP&L CRP&L
      1234PersonA100017%800-30050015-105
      1234PersonB200033%000000
      1234PersonC300050%30000300015-50-35

       

      Question: How can I create the heading groups (i.e. Balance, Unvalidated Balances, P&L above)?

       

      I considered turning the cross table into a straight table and applying a mapping to the value type to make this a stored value that could be used as dimension. However, in addition to wanting to avoid manual mappings using data not able to be extracted with the source data, the data volumes made it impossible as I ran out of RAM.

       

      Any ideas?

        • Value (Expression) Field Groups
          Rahul Gupta

          Hey Hi,

           

          You can use text objects instead...

           

          Or

           

          make an inline of :

           

          Load * Inline [

          Heading, SubHeading

          Balances, Balance

          Balances, [% Of Total Balances]

          ......

          ];

           

          Regards

            • Value (Expression) Field Groups

              Thanks Rahul,

               

              My preference is not to use inline. Could you perhaps expand on what you mean by text objects?

               

              Many thanks

                • Value (Expression) Field Groups

                  I have experiance doing this in Crystal mainly, but I have done it in Qlikview.

                   

                  You values need to have a field that you can reference that will put it in each group. You would then create a formula that displays a text field if it falls in that group.

                   

                  Here is an example I created to sort Order amounts by value:

                   

                  If(orderamt<25000,'1. Under 25K',If((orderamt>=25000 and orderamt<50000),'2. 25K–50K',If((orderamt>=50000 and orderamt<100000),'3. 50K to 100K',If((orderamt>=100000 and orderamt<200000),'4. 100K to 200K',If((orderamt>=200000 and orderamt<300000),'5. 200K to 300K',If((orderamt>=300000 and orderamt<400000),'6. 300K to 400K',If((orderamt>=400000 and orderamt<500000),'7. 400K to 500K',if(orderamt>=500000,'8. Over 500K'))))))))as OrderLvl,

              • Value (Expression) Field Groups
                Anand Chouhan

                Hi jonwebster,

                 

                Try to use the variables to create a expresion and then use it on the straight table as a expression.

                 

                Anand