Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Aggregated Minimum value over 6 months

I am trying to show the Minimum value in a field if a criteria is met aggregated by each month. Right now the minimum across the data set is what is pulling in my bar graph for each month, but I need those to be aggregated to change based off of the minimum for that month only.

I'm currenlty using the below to pull the minimum Layer number if the for all records that have "Manager" in as the criteria. This is currenlty showing me 4 for every month - however, May should have 5 since there is no one in that month that meets that criteria - but I'm still showing 4. Any help is greatly appreacited!

 =Min(if([Rolling12_HC.Manager? Y = 1]='Manager',[Rolling12_HC.Number of Layer]))+1
1 Solution

Accepted Solutions
MVP

Pull it with an equal sign

count({<[Rolling12_HC.Number of Layer]={">=\$(=VMinLev)"}>}[Rolling12_HC.Employee ID])/count(if([Rolling12_HC.Manager? Y = 1]='Manager',[Rolling12_HC.Employee ID]))

or

count({<[Rolling12_HC.Number of Layer]={">=\$(=\$(VMinLev))"}>}[Rolling12_HC.Employee ID])/count(if([Rolling12_HC.Manager? Y = 1]='Manager',[Rolling12_HC.Employee ID]))

19 Replies
MVP

Would you be able to share some raw data or app to show what you have...

Anonymous
Not applicable
Author

Hi Sunny,

I did notice that I get the correct chart when I put the above formulat directly in the measure expresson versus creating the variable, and then calling the variable in the chart. Do you know why that may be? My Variable is named VMinLev and I'm calling it like =\$(VMinLev) in the chart. When I do that I get 4 for every month, when I put the expression directly in as the measure I get 4,4,4,4,5 (which is correct)

I can't share any of the data but I could help describe fields if you ned.

MVP

Are you saving the variable with an equal sign? Can you store the variable without the equal sign and see if that works?

Anonymous
Not applicable
Author

I have a more complicated expression I'm ultimately trying to do and that's why I had created the variable to call and clean up the longer expression:

count({<[Rolling12_HC.Number of Layer]={">=\$(VMinLev)"}>}[Rolling12_HC.Employee ID])/count(if([Rolling12_HC.Manager? Y = 1]='Manager',[Rolling12_HC.Employee ID]))

Do you know how I could incorporate Min(if([Rolling12_HC.Manager? Y = 1]='Manager',[Rolling12_HC.Number of Layer]))+1 into the if [Rolling12_HC.Number of Layer] is greater than the result?

Anonymous
Not applicable
Author

Sunny,

It works not if I just call the variable in a chart without storing the equal sign. However, now when I call the variable in the more complex expression it doesn't work and pulls 0's.

MVP

Pull it with an equal sign

count({<[Rolling12_HC.Number of Layer]={">=\$(=VMinLev)"}>}[Rolling12_HC.Employee ID])/count(if([Rolling12_HC.Manager? Y = 1]='Manager',[Rolling12_HC.Employee ID]))

or

count({<[Rolling12_HC.Number of Layer]={">=\$(=\$(VMinLev))"}>}[Rolling12_HC.Employee ID])/count(if([Rolling12_HC.Manager? Y = 1]='Manager',[Rolling12_HC.Employee ID]))

Anonymous
Not applicable
Author

Sunny,

Thank you so much! That did the trick! You saved me such a headache! Thank you!

Anonymous
Not applicable
Author

Sunny,

Unfortunately still having issues with this formula. If I stop trying to call the variable and just try to type in a minimum criteria could you help me with why the below isn't working:

count({<[Rolling12_HC.Number of Layer]={">=\$Min(if([Rolling12_HC.Manager? Y = 1]='Manager',[Rolling12_HC.Number of Layer]))+1"}>}[Rolling12_HC.Employee ID])/count(Distinct(if([Rolling12_HC.Manager? Y = 1]='Manager',[Rolling12_HC.Employee ID])))

MVP

Are you looking for a single min value or this min value needs to change based on dimension?

Community Browser