I need your help on this. We have the following requirement.
We have applications table at transaction level.
Each row has a application number, entry date,balance, status flags,branch details etc.
Now we have to show the ratio of number of loans 'Closed' to numer of loans 'Active'.( We have Flags to determine this 2 categories).
Our dimension will be Month and expression is Count(of all Closed in that month)/Count(All active till that month)
Then compare this on the same chart with a plan value, which is aggregated at branch level. My plan values will be for a month and branch.
If in a month, MAR 2010 there a 7 apps closed for branch A, and there are 2000 loans 'Active' till Feb 2010, then we have to show the ratio as 7/2000. We will have the value of Plan in an excel which will be like for MAr 2010, branch A, Closed=10, active=2500, hence ratio=10/2500.
My problem:
I am not able to compare the actual an plan with the same month, may be because my data is at different level one at aggregation(plan) and one at transaction (actual).
Even if I try to aggregate the loan table at a branch,Month level...this will mean I have to create separate aggregated tables for all charts.( I have 5 different charts to prepare out of the same data).
Please let me know what should be a right approach.
Attached is one solution, assuming I understood the question (doubtful). For future questions, it's much easier to answer your question if YOU provide the script to generate a data model. Also, then you don't have to deal with mistakes people make trying to guess your data model.
Attached is one solution, assuming I understood the question (doubtful). For future questions, it's much easier to answer your question if YOU provide the script to generate a data model. Also, then you don't have to deal with mistakes people make trying to guess your data model.
People are often told to remove synthetic keys because when you're new to QlikView or data modeling, synthetic keys typically appear by accident and as a result of data model problems. Removing synthetic keys is at least educational, and often resolves the underlying data model problem as well. But they aren't in and of themselves bad. If it is appropriate to connect two tables together by two or more fields, it is appropriate to use a synthetic key.
In this case, I'm connecting the Transactions and Plans tables together. The level at which those two tables can be compared is at the branch and month level. So connecting those tables together by branch and month seems correct to me, at least without knowing even more about the data. If so, that would make this an appropriate use of a synthetic key, so it shouldn't cause any problems, and should be considered the preferred way of modeling the connection.
