Hello,
I am just looking for high level opinions on design consideration when I need to manually add dimensions to a dimension table, as I wonder if the way i have started to do this may not be the most efficient.
Lets say you have a very basic model-- main fact table and a small dimension table joined on a field called DimensionID.
In this dimension table there are multiple sets of 2 specific dimensions that ultimately need to be summed and included in the SAME chart/pivot table as their 2 sub-dimensions.
For example, DimensionID 1 and 2 refer to Food Sales and Ticket Sales. The end goal is to create a chart/table that shows not only the sum of food and ticket sales separately, but also a NEWLY created dimension called "Total sales" which is simply a sum of the 2. There are several other dimensions in this table that need to be summed to show a sub total as well.
I have created a manual table with a field called NewDimension and right joined it ot the existing Dimension table, thus adding my newly created fields like "Total sales" and a dummy dimension ID... but in order to get these newly created dimensions to show up in a chart or table via the "NewDimension" field, it requires a ton of if/then logic, set analysis, and in some cases it still doesnt work clean.
Bottom line, if the ultimate goal is have both your standard dimensions AND a new subtotal of a few standard dimensions existing in the same table, is there an easier way to do this? Sounds simple on paper, as the point is to mirror an existing excel sheet...but in excel its super easy to simply add new rows/columns at will and input a formula... not so easy in qlikview. i dont know if there is some sort of "manually add a subtotal/grouping row in a chart" that essentially creates a new row in the middle of your table and adds a sum of 2 specified dimensions.
.png "Former Employee"){kind=icon}
