Count in Calculated Dimension

[jarebpat]

I am having problems adding a formula in the calculated dimension. The plan is to have the dimension calculated by the totals of quantity and frequency of a product, with the results being different categories such as Class A, Class B, Class C and so on. This would become a "new" calculated category assigned as it is calculated from the current reloaded data.(These categories exist on the data tables coming in.)

Here is the formula that seems to work as expression: if(Count(DISTINCT{$<Rolling12Mth={1},[Qty Sold]={">0"}>}Month)>'5' and Sum([Sales])>7999,'Class A') ; and so on...

formula explanation: if the sale frequency is more than 5 months with a sale above $0 in the last 12 rolling months and have sales value more than $7999, then categorize is as "Class A".

I want to use this in dimension level so I can show $ values as expressions based on the dimension category, this would permit me to see the categories and $ values instead of having at expression level and showing only product level.

Is this the best method? Would there be a better way to do this? FYI the IF statement would continue with further calculations for the other categories in the same formula.

Thanks!

[Anonymous]

IMO, the best way to do this would be to add a calculation to the load script.  Your dashboard performance may take a serious hit if you're trying to do this via a calculated dimension, especially if that is only a piece of the code you want to represent that dimension.  You should be able to achieve the same result using if statements in the load, rather than getting into the messiness of set analysis in a dimension.  If you post a sample of your app, I can take a look for you.