Problems with use of "and" within a sum if expression
Dear Community
I have a query regarding a dashboard I'm trying to develop related to a portfolio of loans. The attached files illustrate my problem using dummy data.
The loan data comes in 2 files as follows:
The non time dependent file: shows the portfolio group the loan sits in (see variables RunID and RunDescription), the borrower's ID number (PersonID), the year the loan was issued (IssueYear), the person's gender and a loan category identifier.
The time dependent file: shows the RunID, RunDescription and PersonID again, a tax year identifier for each record, an "Active" flag (1 in all years), plus a status code (A,B,C or D) for each tax year in the timeline.
In the Qlikview file, the first chart shows the percentage of loan holders in each status code in each tax year, with a restriction to only include borrowers from runID 999 (other runIDs can be selected by changing a variable named "RunSelection"). This chart is working just fine. The syntax for the expression is as follows:
Sum (if(RunID = RunSelection, Active))/Sum (total <[Tax Year Ending]> if(RunID = RunSelection, Active))
The second chart illustrates my problem. I'm trying to add a second condition so that the chart only includes records from tax years after the year in which the borrower's loan was issued. I thought could be achieved in a straightforward manner by simply including an and operator within my sumif as follows:
Sum (if(RunID = RunSelectionand ([Tax Year Ending] > IssueYear), Active))/Sum (total <[Tax Year Ending]> if(RunID = RunSelectionand ([Tax year Ending] > IssueYear), Active))
As you can see from the file, the second chart is returning "No data to display".
Can anybody advise on why this might be happening, and suggest a method to fix the problem, either by changing the expression or the script? Please note that there are a few restrictions on what I am able to do as follows:
The real life data is very large and it is not possible to change the structure of the input data before loading it into Qlikview
The RunID shown in the chart must be chosen using a variable, not a selection from a list box. This is because I eventually want to be able to compare the results from different RunIDs side by side on the dashboard. This will be done by creating 2 copies of the charts, with each copy referring to a different variable.
