Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Expression Headers

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

10 Replies

Re: Expression Headers

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

Not applicable

Re: Expression Headers

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

Not applicable

Re: Expression Headers

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

Re: Expression Headers

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

Not applicable

Re: Expression Headers

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

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

Not applicable

Re: Expression Headers

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

Not applicable

Re: Expression Headers

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

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

Community Browser