Hi!
I have a problem defining a set expression to do what I want it to! Go figure!
Short intro:
I have a data set that holds two ways of identifying the company a row/transaction is adherent to.
- The first way is directly in the dataset via a company name column (in this example this is used for the "actuals" dataset - see attached example data)
- The other is via a department code in the dataset - which can be used to identify the company via a separate dimension table. (In this example this is used mainly for "budget" data)
Issue:
What I'm trying to do is make my app aggregate the sum of sales based on a single filter-selection in my app. (I have other aggregates that use this filter, so I'd rather avoid using two different filters)
- So I have a filter called "Company" which is set to filter by the company-column in the dataset - I'd also like to use this filter ("Company") to define my selection of the budget transactions, and I'd also like to be able to filter the dataset on Department.
e.g. using a set expression something like this
1 SUM({$<
2 [Company]=, // I'll not allow Company-selection to return my dataset blank!
3 [Company.Budget]=[Company], // The company-data in the two company tables are identical. so it should be used here aswell.
4 [Dataset]={'Budget'} // The dataset to be used.
5 >} Sales )
However. This set expression will sum to '0' if I only choose a "department" or if no Company-filter is chosen - Since line number 3 dictates that the data should be limited to the selection of available fields in the Company dimension.
(note: I've managed to solve the problem using some if-statements, but for the actual 30 million row dataset, this is pretty useless as performance is reduced to zero)
Is there anyone out there who could help me steer me on the right path for finishing this problem?
Example dataset:
[Transactions]:
Load * inline
[Rownum, Company, Dataset, Department, Sales
1,Evil Megacorp Inc,Actuals,1,100
2,Evil Megacorp Inc,Actuals,2,100
3,Evil Megacorp Inc,Actuals,2,100
4,Evil Megacorp Inc,Actuals,,100
5,Supernice Corp,Actuals,3,100
6,,Budget,1,99
7,,Budget,2,50
8,,Budget,2,99
9,,Budget,3,100
];
[Dept]:
Load * inline
[Department, Company.Budget
1,Evil Megacorp Inc
2,Evil Megacorp Inc
3,Supernice Corp
]
