I’m creating a report and the report contains multiple queries. For example, the first query pulls a specific set of data (ex. Status, Desc, ID Count, Start Date, Close Date) and a prompted Begin Date and End Date. The second query is a copy of that one but the I created a formula that takes the prompted dates and subtracts a year of the prompted year portion of the date. Basically, the first query is giving me current data and the second is giving me the same data but from a year prior.
When I pull in the Count field, it works as expected. But, I created a measure to calculate the Avg Days between Start Date and Close Date for current and previous. I drag in an text cell, one for current and one for prev, and put the formulas in each one. It works fine. The problem comes when I add a specific status to that formula (ex. =[Ineligible Status Avg (Current)] Where ([Ineligible Reason] = “ASSIGN”). When I add (Where ([Ineligible Reason] = “ASSIGN”) ) to the formulas I get the same results (ex. both fields give the same result of 330 days) for both the current and previous. Any idea why? Could it be because both queries are doing aggregate funtions based on the same field?
I hope I explained this correctly.
bull76 (BOB member since 2010-04-08)