Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
Does it still show the red wiggly line if you do this John?
Only({1<AcctNum=P(AcctNum)>} AcctNum)
So based on the data you have provided, what would be an output based on selection? Or is this not a good database to point that out? I would say provide a sample with expected output and let us play around with it a little bit.
Best,
Sunny
Thanks Sunny,
Going off of my example above:
DateDue: Pkey MonthCreated AcctNum DateDue
11/3/2016 1234 Oct 4567 11/3/2016
11/3/2016 7894 Oct 7895 11/3/2016
Assuming the user clicks 11/3/2016 on the left in the listbox. Usually there are many options, but here there are two options available on that due date. This is great for filtering what Accounts are due that day, but let's say the user, in the context of efficiency, want's to clear up all of the data in the queue for that specific Account. Qlikview is only showing AcctNum based on that DateDue.
What if, our data looks like this..
DateDue: Pkey MonthCreated AcctNum DateDue
11/3/2016 1234 Oct 4567 11/3/2016
11/3/2016 7894 Oct 7895 11/3/2016
11/15/2016 5894 Oct 4567 11/15/2016
If the user selects DateDue 11/3/2016 from the list box on the left, the third observation disappears. I would like this observation to show either A) within the same table box, just as a subset(?)/cascaded values underneath, displaying the Pkey and the DateDue, or B) have another table box/ view available that would show all AcctNum's that correspond to any given AcctNum on the date the user selects..
I hope this makes sense..
Not sure if it's exactly what you want, but perhaps use this set expression in any aggregations in the chart?
{1<AcctNum=P()>}
See attached example.
To elaborate further, so, when a user selects the DateDue, One box would show a list of all AcctNum's, with their corresponding Pkey, that are due on that day. On the other hand, in another box, would be a list of all of the AcctNum's that were contained in the first box after the user made their selection, but the DateDue may not necessarily be the same DateDue the user selected, as well as having different Pkey's..
John, this is great. I have never heard of the P() function and will have to research it, your formula seems to be working for your data, however it looks like the expression is read by QV as an error, is this a bug? I'm wondering how your formula works when it has errors in the syntax. Also, I am not trying to return a sum of anything, just a list of AcctNum's with their corresponding Pkey back...
Hi,
Add a straight table with the following expression count(if(NewDueDate >=DueDate,PKey)). Add in DueDate again to your table when loading with the alias NewDueDate.
Kamiel
Yes, QlikView has bugs where the editor doesn't recognize the syntax, even though it's valid. In mine, at the top it says that the expression is OK, even though it's highlighting parts of it as bad.
I guess you could move the AcctNum field from being a dimension to being an expression? Yeah, that seems to work.
only({1<AcctNum=P()>} AcctNum)
Does it still show the red wiggly line if you do this John?
Only({1<AcctNum=P(AcctNum)>} AcctNum)
The wiggly line goes away then. My reading of the set analysis help text suggests the field name is optional, though?
( P | E ) ( [ set_expression ] [ field_name ] )