Nested Setanalysis / Advanced aggregation

Tue, 22 May 2012 15:08:06 GMT

Hi everyone!

I again have a problem with setAnalysis, similar to this issue, and hope to get some help (and explanation) here…

I have

- Relations

- Transactions

I have a calculated dimension (to make sure relations are grouped per when the relation was last contacted ( =aggr(max(YearMonthContactDate),%RelationNumber_key)

)

What I want to do is to count the number of relations that have exactly 3 negative payments in the database (I need a column for “exactly 0 negative payments, exactly 1 negative, exactly 2 negative etc etc).

My example is only about relations havingexactly 3 negative payments… But… Only those negative payments must be counted wherethe [Action TransactionDate] lies after the first [ActionTransactionDate] of this relation (for the selected %ActionCode)

This is my current expression. At first sight it seemed to work, but there is a bug in it and the clue lies in that "lies afther the first [ActionTransactionDate] " part...

=sum(if(
aggr(
//count, PER %RelatieNummer_key the number of [Actionamount]count(
{
$<
//[Action amount] must be negative (For example -15) [Actionamount]={"<0"}
//the Date of this amount must lie AFTER the minimum[BoekingsDatum Actie] of this relation/%ActioncodeKey ,[ActionTransactionDate]={">=$(=min({<[Actionamount]={'>0'},ActionSourcecode=p(%ActionCode_key)>}[ActionTransactionDate]) )"},
// also take into account the negative values of ANYactionCode (as long as it is after the minimumdate) %ActionCode_key=
>}
[Action amount]
)
,
%RelationNumber_key
)
//EXACTLY 3 payments=3
,1,0)
)

When I write it down in text it may be a bit difficult to follow. In the attached Qlikview I have attached a small project where the problem is visible. Additionally I added some screenshots and explanations that hopefully makes clear how it should work.

Any help is greatly appreciated (as well as good references for further reading about making these kind of aggregations...)

Thanks in advance!

Roberto

PS: I tried to follow the logic of this topic as well, because I think it is similar, but I didn't get the clue of that setAnalysis expression (yet). Any additional explanation is welcome!

Nested Setanalysis / Advanced aggregation

Thu, 24 May 2012 12:21:40 GMT

Hi all,

Since there are no replies yet, and this problem is quite urgent for my customer, another message from my side. I'd really like additional info that may help me solve this issue (in whatever way)

My current way of trying (and I think it should work!) is use Set Analysis. Especially my problem is how I can make sure Qlikview does not take the minimum date of the _entire selection_ but the minimum date that exists inside my dimension...

All clues ideas etc are more than welcome!

Thanks in advance!

topic Nested Setanalysis / Advanced aggregation in QlikView

Nested Setanalysis / Advanced aggregation

Nested Setanalysis / Advanced aggregation