topic Days/Weeks and Total in App Development

Days/Weeks and Total

carlcimino — Wed, 01 May 2024 19:11:17 GMT

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.

Re: Days/Weeks and Total

igoralcantara — Wed, 01 May 2024 19:13:53 GMT

Can you add more context? What exactly is not working? What do you expect, what calculation? What are you getting instead?

Re: Days/Weeks and Total

carlcimino — Wed, 01 May 2024 19:28:50 GMT

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.

Re: Days/Weeks and Total

igoralcantara — Wed, 01 May 2024 19:49:48 GMT

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
)

Re: Days/Weeks and Total

carlcimino — Wed, 01 May 2024 20:06:25 GMT

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 ) )

Re: Days/Weeks and Total

igoralcantara — Wed, 01 May 2024 20:21:58 GMT

As an exercise, use my expression but replace the Sum for a Max just to see what we get.

Re: Days/Weeks and Total

carlcimino — Thu, 02 May 2024 16:58:58 GMT

@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
))

Re: Days/Weeks and Total

igoralcantara — Thu, 02 May 2024 17:34:38 GMT

Glad to hear!