
How can I calculate the number of dimension 2 divide by the number of dimension 1
Stefan Wühl Feb 4, 2012 6:59 PM (in response to Laurent DOURNAUX)At first glance, I would have recommended something like
= count(User) / count(TOTAL<FY> User)
where User is you field for user and FY is your fiscal year dimension field.
Depending on your needs, you may want to add a Distinct qualifier, like
=count(distinct User) / count(distinct TOTAL<FY> User)
Maybe I am misunderstanding something, but shouldn't your percentages sum up to 100% in one fiscal year? In 2012, I sum up to 110%.

How can I calculate the number of dimension 2 divide by the number of dimension 1
Laurent DOURNAUX Feb 5, 2012 6:41 AM (in response to Stefan Wühl )Thanks swuehl.
That is what I wanted. It is my mistake for 2012, it should sum up to 100%.

How can I calculate the number of dimension 2 divide by the number of dimension 1
John Anderson Feb 5, 2012 10:39 PM (in response to Stefan Wühl )Just out of curiosity, what does the <FY> syntax do? I understand it represents the Financial Year field but is it correct in the form displayed?
Regards,
John.

How can I calculate the number of dimension 2 divide by the number of dimension 1
Laurent DOURNAUX Feb 6, 2012 3:42 AM (in response to John Anderson)The <FY> will count only the total for each FY.

How can I calculate the number of dimension 2 divide by the number of dimension 1
Stefan Wühl Feb 6, 2012 4:32 AM (in response to John Anderson)John,
please let me just cite (from the Help) the syntax of count here:
count([{set_expression}][ distinct ] [ total [<fld {, fld}>]] expression)
If the word distinct occurs before an expression, duplicates resulting from the evaluation of this expression will be disregarded.
If the word total occurs before an expression, the calculation will be made over all possible values given the current selections, but disregarding the chart dimensions.
The total qualifier may be followed by a list of one or more field names within angle brackets. These field names should be a subset of the chart dimensions. In this case the calculation will be made disregarding all chart dimensions except those listed, i.e. one value will be returned for each combination of field values in the listed dimension fields. Also fields which are not currently a dimension in a chart may be included in the list. This may be useful in the case of group dimensions, where the dimension fields are not fixed. Listing all of the dimensions in the group causes the function to work when the cycle or drilldown level changes.
//end citation
So the syntax is correct, <FY> is the list of field names to the TOTAL qualifier. So one value per FY field value will be returned from count function.
It is not the same as a field name "guard" like [], e.g. used with field names containing spaces.
Hope this helps,
Stefan

