Hi, I'm wondering is someone could help us with the following: We have a transaction module called "Data Store" which is made up of line items (columns) and a Transaction Unique Key (List - Rows) Refer "Image 1" for the Pivot View... Refer "Image 2" for the Model view. ( This is a subset of the data contained in the module, first column being the unique ID) There are 84 line items in the model, for each Unique Key there and there are 20 line items of which only one line item per Unique Key is populated (currently with a value, which is duplicated from the "Amount" line item), leaving 19 line items not populated resulting in a large amount of sparsity. In order to reduce the size of the module we would like to reduce the 20 line items down to one line item, with the new line item containing the name of old line item (or something like that), let's call the new line item "Category". In another module, which already exists, we would like to be able to sum up the amount from the Data Store module based on time period, branch name, division and category (there is one more line item which I have left out of the examples as it will work the same way as the branch name and division etc and not required, however handy to know it exists). Something else to note is the formula is to be written in a line item which is not list formatted, it's just a line item, which cannot easily be changed. Normally we'd use a formula such as: Data Store.Amount[SUM: Data Store.Time Period, SUM: Data Store.Branch Name, SUM: Data Store.Division, SUM: Data Store.Category] However this will not work as "SUM: Data Store.Category" cannot go down that one step further (to something like: "SUM: Data Store.Category.CAT1" for the contents of the cells. Even using a SELECT won;t go down to that level, otherwise that would be ideal. We could create an intermediate module with a list formatted Dimension for the Category, however there are 5 dimensions, then create the formula to read from that, however even when sub setting the dimension lists the cell count of the intermediate module is larger than the cell count of the Data Store. Has anyone come across this sort of issue before and how did you manage to resolve it ?
... View more
