Work-Around for QlikView Associative Logic

[lucasdavis500]

I'm trying to work-around, but work with ( I think? ), Qlikview's associating logic. It's hard to explain without an example, so here's one..

I have some data: Some dates, Some Pkey, and Some Account #'s

Below data(DATE FIELD) is in a ListBox:                           Below Data is in a TableBox:

DateDue:                                                          Pkey     MonthCreated     AcctNum         DateDue  
11/3/2016                                                            1234               Oct               4567          11/3/2016

11/4/2016                                                            2345               Oct               2345          11/4/2016

11/5/2016                                                            3456               Oct               2589          11/5/2016

11/6/2016                                                            4567               Oct               7845          11/6/2016

My user has a list box (on the left) that they can select a date (DateDue), When they make a selection, my TableBox (Right) shows a list of all of my AcctNum's with a due date of that date (usually that day or the next day). This is great, it is easy to find Account Numbers by their due date to prioritize. The problem, however, is I would like my user to know when there are more Accounts due on a different date than that particular due date, and have a different Pkey, but the same AcctNum. Ideally, when the user selects the DateDue, they would have a list of all of the accounts that are DUE on that Date, PLUS a list of accounts that correspond to the SAME Account Number.

I'm sure this is possible with qlikview, I just don't know how to stop QV from associating those DateDue's and Associate the AcctNum...

[sunny_talwar]

Oh ya, it is optional. This was just to get rid of the wiggly line

[johnw]

Ah, OK. I've just learned to ignore the wiggly lines. QlikView just isn't fond of the way I write my expressions.

[lucasdavis500]

Sunny, that did work

Do you know how I can do this to include only the last 60 days? I have this expression...

=Only({1<Month = {"$(>=Month(Today() - 60))"} ,  AccountNumber=P(AccountNumber)>} AccountNumber)

but this doesn't seem to work....I thought placing the GT equal to symbol in set analysis was correct

[sunny_talwar]

Try one of the two options

1) Only({1<Month = {"$(='>=' & Month(Today() - 60))"} ,  AccountNumber=P(AccountNumber)>} AccountNumber)

2) Only({1<Month = {">=$(=Month(Today() - 60))"} ,  AccountNumber=P(AccountNumber)>} AccountNumber)

UPDATE: Thing to make sure here is that if you have more than 1 year of data, the above expression will pick all year data from Sept, Oct, Nov, and December. Is this what you want or do you want just Sept, Oct, Nov data for this year?

[lucasdavis500]

Good catch... the most current year..

[lucasdavis500]

so probably add year = {(=TODAY(0))}? I want the most 30 days, from the most current year..

[sunny_talwar]

May be like this:

Only({1<Month = {"$(='>=' & Month(Today() - 60))"}, Year = {"$(=Year(Today()))"}  AccountNumber=P(AccountNumber)>} AccountNumber)

[lucasdavis500]

Right, but I see a problem arising in January, when they still need last 30 days, but last 30 days may be in the last year. But I also don't want the user to see consecutive September data from previous years.... do I have to pick the lesser of the two evils here?

[sunny_talwar]

No, the best option would be to get rid of month and year field and use dates to do the job for you

Only({1<Date = {"$(='>=' & Date(Today() - 60, 'DateFieldFornatHere') & '<=' & Date(Today(), 'DateFieldFormatHere'))"},  AccountNumber=P(AccountNumber)>} AccountNumber)

[johnw]

Or like this, avoiding date field formats and dollar sign expansions and doing a straight numeric compare, assuming I got the syntax correct. I'm, uh... syntactically challenged sometimes.

only({1<Date={"=Date>=today()-60 and Date<=today()"},AccountNumber=P()>} AccountNumber)