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!
