Aggregated Minimum value over 6 months

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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.

[sunny_talwar]

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

[Anonymous]

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]

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.

[sunny_talwar · Accepted solution]

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]

Sunny,

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

[Anonymous]

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

[sunny_talwar]

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