I will do my best to explain my data model and ask for peoples thoughts and suggestions.
I come from a relational database background so like seperate tables for my descriptions etc, but having also worked with Qlikview I found in the BI space merging tables into single tables has its benefits (Like being able to easily define hirarachy).
Here it goes..
"Item" Table
Item Code, Item Description...... numerous other fields related for Item Info.
"Category" Table
Category Code, Category Description
"Sales" Table
Sales Ref, Item Code, Category Code, Date, Qty, Price, Value
Functions I want to be able to facilitate.....
1) A hierarchy of Category Description, Item Description.
Each item is part of a category, but that category can change. The sales history doesnt change and update because its was a recognised sale while it was part of that category. The only way to get a hierarchy with them both in it is to have the descriptions in the sales table. But in doing this I have problems with the Ranks and drill through page to "Item Details".
2) Drill through page from the "Item Description" (Which I have to add to achieve 1)) in the hierachy above to a page for the Item table. For this to work there is a requirement for a "Both" ways relationship between the Sales and Item tables.
Ultimately Id love not to have a two way relationship on a lookup table but not sure I can avoid given my need for the hierarchy above.
As the description in the Sales Table is filtering the Items table. I change it to a Both ways relationship and it filters the drill through page fine. - However my Rank below stops working..
3) RANKS - I am really having problems with rank forumlas in this example.
While I get the ranks dispalying correctly when all Items are selected, it displays a rank of 1 on everything when I use the "Item Description" to filter the Sales Table for drill thru (below) onto the Items Details page as there is only 1 selected record in the items table. Ive used the RANKX "ALL" parameter to apply the rank to All "Item" table items but the formula still returns 1.
I have tried ALL on the Sales and ALL on the Item details table without luck.
My rank was working well when I was filtering the Item table in the drill through.
However when I moved the drill through to the Sales Table to pick up the Category and Stock Descriptions, and changed the Sales Table stock description to the drill through - the Ranks remain always as 1.
It just seems that everytime I fix one area I break the other hence my frustration and this post.
Any assistance or suggestions welcome.
