Hi All,
I need to calculate the Hires% for Current month vs Rolling 12 Months in a bar chart with two dimensions.
Dimensions:
1. MonthName(PeriodDateThru)
2. Ethnicity
The chart needs to toggle between Current Month and Rolling 12 Months, the axis should display 12 month even for Current Month with each month displaying concrete values for that respective month. The chart works fine for sum(hires) and sum(total employees).
Expressions:
Hires(%) -
sum({<$(vPeriodType), $(vAsofPeriod), PeriodMonthAbbreviatedName=,PeriodYear=
, TimeFrame = {'Current Year'}>} Hires)
/
(sum({<$(vPeriodType), $(vAsofPeriod), PeriodMonthAbbreviatedName=,PeriodYear=, TimeFrame = {'Current Year'}>}[# HeadCount])/12) - (Dividing by 12 as I want concrete set of values for each month and Rolling 12 calculation, aggr or Total didn't work.)
vPeriodType - enables switch between Current month and Rolling 12 months
vAsofPeriod - AsOfPeriodDateThru={">=$(=date(MonthEnd(AddMonths(max(num(AsOfPeriodDateThru)),-11)),'MM/DD/YYYY'))<=$(=date(max(num(AsOfPeriodDateThru)),'MM/DD/YYYY'))"}
The chart needs to display % values for each month broken down by Ethnicity bands.
When using the second dimension (Ethnicity) the expression is not evaluating the way I want it to and displays incorrect numbers.
Need help with the set expression to display right % values.
Regards,
Shweta