topic Re: Dynamic Set Analysis Expression in QlikView

Dynamic Set Analysis Expression

Wed, 29 Apr 2015 20:18:40 GMT

Hi guys,

I'm hoping you can help me with this. I'm building an application that allows the end user to specify a 'From' and To' date using the Input box object which then drives the period the transaction amounts need summing up. However the requirement is that it needs to only bring back the earliest transaction amount for a category within the user specified date range (From and To dates).

For example:

From	15/01/2015	= vMinTnxDate
To	15/02/2015	= vMaxTnxDate
Dimension	Tnx Date	Amount
A	15/01/2015	£10	First Record
A	16/01/2015	£20
A	17/01/2015	£30
A	18/01/2015	£40
A	19/01/2015	£50
A	20/01/2015	£60
A	21/01/2015	£70	Last Record
B	18/01/2015	£80	First Record
B	19/01/2015	£90
B	20/01/2015	£100
B	21/01/2015	£110
B	22/01/2015	£120
B	23/01/2015	£130
B	24/01/2015	£140
B	25/01/2015	£150	Last Record

My current expression in a Straight Table is:

Sum({<[Tnx Date] = {'>=$(vMinTnxDate) <=$(vMaxTnxDate)'}>} Amount)

This works so far as dynamically summing up the amount for each dimension within the date range. What I need the expression to do is take the records within the user date range and then just show the amount in the first record for each category (A & B) in this case £10 for category A and £70 for category B. and in a separate column show the latest amount for the respective categories.

I have come across this expression which works to an extent:

=Sum(TOTAL <Dimension>

Aggr(

If([Tnx Date] = Min(TOTAL <Dimension> [Tnx Date]),Amount),

Dimension,[Tnx Date])

)

Any help with this would be appreciated,

Many thanks

Re: Dynamic Set Analysis Expression

Anonymous — Wed, 29 Apr 2015 20:28:46 GMT

I think the function FirstSortedValue() will help you. Take a look.

Re: Dynamic Set Analysis Expression

maxgro — Wed, 29 Apr 2015 20:52:52 GMT

try with a str chart

Dimension

FirstSortedValue({<[Tnx Date] = {'>=$(vMinTnxDate) <=$(vMaxTnxDate)'}>} total <Dimension> Amount, -[Tnx Date])

FirstSortedValue({<[Tnx Date] = {'>=$(vMinTnxDate) <=$(vMaxTnxDate)'}>} total <Dimension> Amount, +[Tnx Date])

Re: Dynamic Set Analysis Expression

Thu, 30 Apr 2015 08:30:26 GMT

Hi Michael,

many thanks for the quick response, I will try your proposed solution. With regards to efficiency, does the FirstSortedValue function cope well with high volumes i.e. when we hit the million mark transactions/records? just wondering what consideration I would need to take when it's scaled out.

Many thanks

Re: Dynamic Set Analysis Expression

maxgro — Thu, 30 Apr 2015 08:46:04 GMT

a million usually isn't a high volume

tries to mimic (makes some test data) the volumes that you will have in a few years

Re: Dynamic Set Analysis Expression

Thu, 30 Apr 2015 09:32:21 GMT

Thanks Massimo

If I have to include more than one dimension, is it simple as adding it within the dimension list just after the TOTAL function.