Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have daily office visit data and a weekly census for that location. How would I do the following? I'm thinking I need to use a Total function but it doesn't seem to work.
Visits Syntax:
Count({<Year={$(yearselect)},Source-={'PSHR'},[Location Description]-={'Work From Home'},[Pay Status]={'Active'},Visit_Swipe={0}>} distinct [Employee ID])
Assigned Seats Measure Syntax:
Count( {<Year={$(yearselect)},Source={'PSHR'},[Location Description]-={'Work From Home'},[Pay Status]={'Active'}>} distinct [Employee ID])
Attached some sample data for reference.
I believe Aggr can help you. Try something like this:
Sum(
Aggr(NoDistinct
Count({<Year={$(yearselect)},Source={'PSHR'},[Location Description]-={'Work From Home'},[Pay Status]={'Active'}>} distinct [Employee ID])
, [Local Description], Year, [Month Name], SwipeWeekStart
)
Can you add more context? What exactly is not working? What do you expect, what calculation? What are you getting instead?
When I add total to the expression...
Count( total {<Year={$(yearselect)},Source={'PSHR'},[Location Description]-={'Work From Home'},[Pay Status]={'Active'}>} distinct [Employee ID])
The same number is in the expected in office column across all the weeks. I expect that number to be the census for that week but it returns something else.
I believe Aggr can help you. Try something like this:
Sum(
Aggr(NoDistinct
Count({<Year={$(yearselect)},Source={'PSHR'},[Location Description]-={'Work From Home'},[Pay Status]={'Active'}>} distinct [Employee ID])
, [Local Description], Year, [Month Name], SwipeWeekStart
)
hi @igoralcantara thanks for your help that returns even more bizarre results. i'll keep trying lmk if you think of anything else.
Sum( Aggr(NoDistinct Count({<Year={$(yearselect)},Source={'PSHR'},[Location Description]-={'Work From Home'},[Pay Status]={'Active'}>} distinct [Employee ID]) , [Location Description], Year, [Month Name], SwipeWeekStart ) )
As an exercise, use my expression but replace the Sum for a Max just to see what we get.
@igoralcantara This and your other suggestion eventually led to the solution. I switched it to be a weekly view but the concept is the same. Thanks so much for your help!
max(
Aggr(NoDistinct
Count({<WeekYear={'$(vMaxWeekSelect)'},Source={'PSHR'},[Location Description]-={'Work From Home'},[Pay Status]={'Active'}>} distinct [Employee ID])
, [Location Description], SwipeWeekStart
))
Glad to hear!