10 Replies Latest reply: Sep 9, 2015 9:41 AM by Andre Toerien RSS

    Expression Headers

    Andre Toerien

      Hi

       

      I want to have expression headers. I have seen some responses which talk about using pivot tables but I have not managed to get this to work.

       

      I have a Total Amount and Number of Accounts column for accounts outstanding for 30, 60, 90 days etcand do not want to create 30DayTotal, 30DayNoOfAccounts, 60DayTotal, 60DayNoOfAccounts. I would rather like to have a header statting 30 days, 60 days, and then have expression under these with amount and number of accounts

        • Re: Expression Headers
          Peter Cammaert

          Please post a (example) document with the data model and if possible a pivot table for which you do want the headers modified..

            • Re: Expression Headers
              Andre Toerien

              I will try to get a sample document out to you as our data is huge, what we have at this stage I can give you in a print screen. You will see the 30, 60, and 90 days with 4 total , it is just a rough draft at this stage. Let me know if you do need actual data to work with

              • Re: Expression Headers
                Andre Toerien

                Hope this makes sense. I have 3 columns I want in each "Main heading": Total, 30Day, 60Etc

                  • Re: Expression Headers
                    Peter Cammaert

                    Well that document may have a data model that is a little bit too simple for our purposes

                     

                    I guess that you aggregate the AccountTotals from a transactions or bookings table. You could change the aggregation so that you get bucket records, eg a record for each bucket with a bucket type indicator and a bucket amount, like

                     

                    Account,   AccountStatus, BucketType, BucketAmount

                    123456789, Final,         30 days,    123456.90

                     

                    Now you can create a pivot table with a left dimansion of Account, and a top dimension of BucketType. The expression simply becomes =Sum(BucketAmount).

                     

                    Now the big advantage of this approach  is that you can call the BucketType values whatever you like. Since there is only one expression in your pivot table, this BucketType dimension will be used as the headers of the Amount columns.

                     

                    Peter

                      • Re: Expression Headers
                        Andre Toerien

                        I think I understand the concept you are getting at. Correct me if I am wrong.

                         

                        I add extra fields example BucketTotal, Bucket30Days, Bucket60Days and then I select it as a diemnsion and drag it to the top in the Pivot sheet.

                         

                        I am still not quite convinced exactly how this will help me get count of the number of counts or percentages. On the number of accounts, do I then add fields BucketTotalAccountNo, Bucket30DaysAccountNo etc.

                         

                        It seems like quite a lot of extra columns to load as we are looking at Amount, number of accounts, %Amount, %NumberAccounts

                          • Re: Expression Headers
                            Peter Cammaert

                            I don't think you are getting the idea (no problem with that, my fault)

                             

                            You want dynamic labeling. One of the techniques to accomplish this is to typify your data using dimension values. For example, if a single account has outstanding amounts in categories "30 days", "60 days" but not in category "90 days", you split the account information into two records (in your script), like this:

                             

                            Account,  AccountStatus, BucketType, BucketAmount

                            123456789, Active,        30 days,    123456.90

                            123456789, Active,        60 days,    987654.12


                            Imagine that you want a pivot table with a horizontal top header line indicating the bucket, and immediately below the expression header (e.g. Total Amount, Count etc.), create one with a dimension on the left called Status, a top level dimension that uses BucketType and two expressions:

                             

                            =Count(DISTINCT Account)

                            =Sum(BucketAmount)

                             

                            The data will be distributed over buckets and expressions as it was in your original example.

                             

                            The only disadvantage of this technique comes from the fact that QV Pivot tables don't have the same flexibility for adding arbitrary expressions as Excel does for instance. They will all be grouped/repeated under the different dimension values.

                             

                            Peter

                              • Re: Expression Headers
                                Andre Toerien

                                That then means switching the view of the data around , from  data with current columns going from current, 30, 60 90, 120 through to 6 years to one where we have a row per period. Unfortunately we need the data as is as well. We have a possible 12 dimension fields which we use to investigate the data.

                                 

                                Maybe if you have an example of how you would do it, unless I just do column naming, fortunately it is only for totals, 30, 60 90 and 120 days where they want to see these sums, counts etc

                                  • Re: Expression Headers
                                    Andre Toerien

                                    I managed to get the Pivot table with one dimension, not sure how to add extras though

                                     

                                    for example

                                     

                                    What I have is

                                          

                                          

                                    Totals
                                    AmountNumberofAccountsAmountNumberofAccountsAmountNumberofAccounts

                                     

                                    What I need is

                                          

                                    Totals30Days60Days
                                    AmountNumberofAccountsAmountNumberofAccountsAmountNumberofAccounts
                                      • Re: Expression Headers
                                        Peter Cammaert

                                        Can you post a new sample document with your solution attempt as described in your previous post?

                                          • Re: Expression Headers
                                            Andre Toerien

                                            I just basically added extra columns for each. I am not sure what will happen to my data if I were to divide it into a single column with totals. As stated in a previous post I still need the info in the original format  with a column for Total Amount, 30, 60, 90, 110 Days, and then a lot of extra periods. Your way looks like a totally different look on how the data is imported.

                                             

                                            Maybe I am not understanding your solution, but I really appreciate your help

                                             

                                            Just to give you an idea of the actual amount of fields involved here is my actual script for the model I am working on. I have a number of graphs exploring the data by financial periods for ageing purposes. we also have a full table with detailed records if they want to do a search on individual records. This is where I see the problem as I cannot change the way the data is aligned, not sure if that is the right word. I am attaching the sample qvw source file so you can maybe show me exactly

                                             

                                            SQL SELECT

                                            Age.[AccountStatus]

                                            ,Age.AccountNumber

                                            ,Age.[MasterAccountNumber]

                                            ,Age.[BillingCycle]

                                            ,Age.[TownshipCode]

                                            ,Age.[Township]

                                            ,Age.[Ward]

                                            ,Age.[AccountTypeCode]

                                            ,Age.[AccountType]

                                            ,Age.[DebtorIndexCode]

                                            ,Age.[DebtorIndex]

                                            ,Age.[GroupDebtorTypeCode]

                                            ,Age.[GroupDebtorType]

                                            ,Age.[DebtorTypeCode]

                                            ,Age.[DebtorType]

                                            ,Age.[UnitNumber]

                                            ,[CurrentBalance]

                                            ,[ServiceGroup]

                                            ,[ServiceCode]

                                            ,[TotallingCategoryCode]

                                            ,[TotallingCategory]

                                            ,Age.[Advance]

                                            ,[Credits]

                                            ,[CurrentTotal]

                                            ,[PreviousTotal]

                                            ,[30DaysTotal]

                                            ,[60DaysTotal]

                                            ,[90DaysTotal]

                                            ,[120DaysTotal]

                                            ,[150DaysTotal]

                                            ,[180DaysTotal]

                                            ,[1800DaysPlusTotal]

                                            ,[210_360DaysTotal]

                                            ,[390_720DaysTotal]

                                            ,[750_1080DaysTotal]

                                            ,[1110_1440DaysTotal]

                                            ,[1470_1800DaysTotal]

                                            ,[TotalExcludingVAT]

                                            ,[TotalVAT]

                                            ,[AccountTotal]

                                            ,[SectionalTitleUnitNumber]

                                            ,[OwnerTenantCode]

                                            ,[OwnerTenant]

                                            ,[OwnerTypeCode]

                                            ,[OwnerType]

                                            ,[AttorneyCode]

                                            ,[Attorney]

                                            ,[AgentCode]

                                            ,[Agent]

                                            ,[CollectorCode]

                                            ,[Collector]

                                            ,[IndigentStatusCode]

                                            ,[IndigentStatus]

                                            ,[PermitPaymentExtension]

                                            ,[PaymentExtension]

                                            ,[PermitCutOff]

                                            ,[CutOffStatusCode]

                                            ,[CutOffStatus]

                                            ,[PermitInterest]

                                            ,[PermitLegalAction]

                                            ,[LegalActionStatusCode]

                                            ,[LegalActionStatus]

                                            ,[VIPAccount]

                                            ,[StaffAccount]

                                            ,[ApplicationDate]

                                            ,[FinalizationDate]

                                            ,[TerminationDate]

                                            ,[ClearanceApplicationDate]

                                            ,[ClearanceValidFrom]

                                            ,[ClearanceValidTo]

                                            ,[ClearanceCertificateNumber]

                                            ,[ClearanceCertificateDate]

                                            ,[Surname]

                                            ,[Initials]

                                            ,[DateOfBirth]

                                            ,[IDNumber]

                                            ,[CiproRegistrationNumber]

                                            ,[CompanyRegistrationNumber]

                                            ,[WorkTelephoneNumber]

                                            ,[HomeTelephoneNumber]

                                            ,[CellphoneNumber]

                                            FROM V_R_DebtorAccountServiceAgeing Age

                                            INNER JOIN V_R_DebtorAccountInfo Act on RTrim(Act.AccountNumber) = RTrim(Age.AccountNumber)

                                            WHERE [AccountTotal] <> 0;