topic Expression Headers in QlikView

Expression Headers

Mon, 07 Sep 2015 12:50:11 GMT

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 — Mon, 07 Sep 2015 12:54:48 GMT

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

Mon, 07 Sep 2015 13:05:03 GMT

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

Mon, 07 Sep 2015 13:40:13 GMT

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

Re: Expression Headers

Peter_Cammaert — Tue, 08 Sep 2015 06:41:48 GMT

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

Tue, 08 Sep 2015 07:01:13 GMT

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 — Tue, 08 Sep 2015 07:50:32 GMT

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

Tue, 08 Sep 2015 12:27:19 GMT

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

Wed, 09 Sep 2015 07:47:41 GMT

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

Re: Expression Headers

Peter_Cammaert — Wed, 09 Sep 2015 12:27:16 GMT

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

Re: Expression Headers

Wed, 09 Sep 2015 13:41:31 GMT

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;