topic Straight Table - Selecting All Records within Calculated Range in QlikView

Straight Table - Selecting All Records within Calculated Range

Wed, 09 Dec 2009 04:04:23 GMT

I am a fairly new user to QlikView and I am in the process of doing some analysis based upon account transactions.

I have created a table by Company (dimension) and added a calculated expression to give the total value of transactions of the company (linking into a selected date/year).

The problem I have is creating a selection box that will select/group each company based upon value. For example I would like to have <10,000 , 10,001 - 25,000 , 25,001 - 50,000 etc etc.

Any advise would be well received.

Straight Table - Selecting All Records within Calculated Range

johnw — Wed, 09 Dec 2009 08:43:50 GMT

If you could settle for equal-sized ranges, something like this as the expression for the selection box:

aggr(class(sum("Transaction Amount"),10000),Company)

If you want to keep your unequal ranges, something like this:

aggr(if(sum("Transaction Amount")<=10000,'<=10,000'
,if(sum("Transaction Amount")<=25000,'10,001 - 25,000'
,if(sum("Transaction Amount")<=50000,'25,001 - 50,000'
,'>50000'))),Company)

I didn't test those. If you can't get it to work, let me know, and I'll try to come up with an example to use to debug it.

Straight Table - Selecting All Records within Calculated Range

Wed, 09 Dec 2009 18:54:09 GMT

Many thanks for this, the first expression works perfect.

The second part appears to collate the totals together,

Straight Table - Selecting All Records within Calculated Range

johnw — Wed, 09 Dec 2009 23:57:03 GMT


Ted11 wrote:The second part appears to collate the totals together,

Well, I'm not sure what you mean by that, so here's a sample file with both the class() and series of if()s working as I'm thinking you'd want. Let me know if I've misunderstood, or if it works in the example but not with your real data.

Straight Table - Selecting All Records within Calculated Range

Thu, 10 Dec 2009 02:36:15 GMT

I have just tried using your example.....it all works perfectly.

Thankyou for your help on this.