Expression Headers

[#1]

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

[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..

[#3]

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

[#4]

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

[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

[#6]

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

[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

[#8]

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

[#9]

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

for example

What I have is

Totals
Amount	NumberofAccounts	Amount	NumberofAccounts	Amount	NumberofAccounts

What I need is

Totals		30Days		60Days
Amount	NumberofAccounts	Amount	NumberofAccounts	Amount	NumberofAccounts

[Peter_Cammaert]

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