Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have an application over HR data that uses a metric for Average Headcount by week. I had the calculations all validated out and published to our server for the better part of a month and then I noticed that they stopped working. The even stranger thing is that they are so similar yet in QV 10 SR3 and QV 11 SR2 the first one calculates and the second does not.
My calculation for a chart over current selections is the following and it yields results. [First Chart]
SUM(AGGR(
COUNT({$<[Employee Status]={'Active'},RECORD_ID= P({$<FISCAL_YEAR={'*'}>})>}DISTINCT [Employee ID])
,[Location Name],Shift,FISCAL_WEEK
))
/
COUNT({<$(='[' & concat({<$Table={"AllEmployees"}>}$Field,']=,[') & ']=')>}DISTINCT FISCAL_WEEK)
The second chart is the same metric over the same month selected the year prior. [Second Chart]
SUM(AGGR(
COUNT({$<[Employee Status]={'Active'}
,Date=,FISCAL_MONTH=,FISCAL_QUARTER=,FISCAL_MONTH_NO={$(=Only(FISCAL_MONTH_NO))},FISCAL_YEAR={$(=Only(RIGHT(TRIM(FISCAL_MONTH),4))-1)}
>}DISTINCT [Employee ID])
,[Location Name],Shift,FISCAL_WEEK
))
/
COUNT({<$(='[' & concat({<$Table={"AllEmployees"}>}$Field,']=,[') & ']=')>}DISTINCT FISCAL_WEEK)
I tried troubleshooting the expression from the second chart with no luck. The denominator seemed to calculate fine by itself. For the numerator I tried substituting my set analysis with FISCAL_MONTH_NO = {2} and FISCAL_YEAR = {2013} directly and it still didn't work. None of the fields in my numerator have null values in the data. I saw an unanswered thread about dual types and I changed my FISCAL_MONTH back to a TEXT only and that didn't help. I tried wrapping all my fields in brackets and that didn't work. I tried the following which is completely simplified and un-dynamic and it yields a zero as well. When I comment out the date fields portion it works. Also when i undo my date selections it works. This is even after I convert all of the date fields to text and trim them. It seems like QV is unable to override or remove the date selections. Any ideas?
SUM(AGGR(
COUNT(
{$<[FISCAL_QUARTER] = {'QTR 1 2012'},[FISCAL_MONTH] = {'March 2012'},[FISCAL_YEAR] = {'2012'},
[Employee Status]={'Active'}>}
DISTINCT [Employee ID])
,[Location Name],Shift,FISCAL_WEEK
))
/
5
FYI: My application consists of two data tables, one that has all changes to employment data 'AllEmployees' and a calendar table for fiscal calendar information 'Calendar'. ALSO, neither the first or second chart calc for that metric in 11.2 SR2 but yield null signs instead of zeros.