I have a dashboard request from a client that has proposals that go through a workflow. They would like to see the amount of time in days spent in each queue as a column in a table. So the columns would be: Proposal ID, Proposal Title, Days in Q1, Days in Q2,...,Days in Q16.
The proposals have a MIKG on them that include "School". Most proposals only have 1 school, some have 2 or 3.
A proposal could visit a Q more than once in it's lifetime, and I want to show the total time spent in the queue calculated across all visits.
I can basically get what I want by using a pivot table with Values = Days, Columns = Q Name, Rows = Proposal ID.
My problem right now is that if there is more than 1 school, the # of days for each Q is multiplied by the # of schools.
I would like to set a calculated field to IIF([# Schools] > 1, [Days] / [# Schools], [Days]), but this can't be saved as an expression. I think it's the division that's failing. Is there a way to do this that will calculate?
I'm also open to a better SQL solution in my data provider, as well, if anyone is willing to look...
