Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am currently using the following expression to pull a 'Rate' for an entire year regardless of selections and it is working: Sum({<Year, Month>}NUM_STATUS)/Sum({<Year, Month>}DEN_STATUS) . What I am wanting to do now is assign a 'Star' value based on where that rate falls on the threshold table (between PtileBeg and PtileEnd). However, I need the formula to point to the max Year and Month's thresholds as they vary each month.
This is what I am trying to use but it isn't working:
if(Sum({<Year, Month>}NUM_STATUS)/Sum({<Year, Month>}DEN_STATUS)>= only({<MaxYear,MaxMonth>}PtileBeg) and
Sum({<Year, Month>}NUM_STATUS)/Sum({<Year, Month>}DEN_STATUS)<= only({<MaxYear,MaxMonth>}PtileEnd),only({<MaxYear,MaxMonth>}Star))
Any help is much appreciated.
Try this
Max({<Year = {$(=Max({1} Year))}, MonthNum = {"$(=Max({1<Year = {$(=Max({1}Year))}>}MonthNum))"}>} PtileBeg)
May be this
If(Sum({<Year, Month>}NUM_STATUS)/Sum({<Year, Month>}DEN_STATUS) >=
Only({<Year = {<$(=Max(Year))>}, Month = {"$(=Max({<Year = {$(=Max(Year))}>}Month))"}>} PtileBeg)
and
Sum({<Year, Month>}NUM_STATUS)/Sum({<Year, Month>}DEN_STATUS) <=
Only({<Year = {<$(=Max(Year))>}, Month = {"$(=Max({<Year = {$(=Max(Year))}>}Month))"}>} PtileEnd),
Only({<Year = {<$(=Max(Year))>}, Month = {"$(=Max({<Year = {$(=Max(Year))}>}Month))"}>} Star))
Thank you, but I am getting errors and am not sure what may be wrong.
I don't know if this is helpful but it might help you get rid of some of the complexity in your expression.
My bad, try this
If(Sum({<Year, Month>}NUM_STATUS)/Sum({<Year, Month>}DEN_STATUS) >=
Only({<Year = {$(=Max(Year))}, Month = {"$(=Max({<Year = {$(=Max(Year))}>}Month))"}>} PtileBeg)
and
Sum({<Year, Month>}NUM_STATUS)/Sum({<Year, Month>}DEN_STATUS) <=
Only({<Year = {$(=Max(Year))}, Month = {"$(=Max({<Year = {$(=Max(Year))}>}Month))"}>} PtileEnd),
Only({<Year = {$(=Max(Year))}, Month = {"$(=Max({<Year = {$(=Max(Year))}>}Month))"}>} Star))
I was hoping this would work but it didn't. I think the issue is because of trying to point to the max month/year Ptile. When I just try to show that value it isn't working either. For example, I'm putting this in a text box just to see the value and am getting an error: Only({<Year = {$(=Max(Year))}, Month = {"$(=Max({<Year = {$(=Max(Year))}>}Month))"}>} PtileBeg)
What error message do you see?
Actually it's just null '-', not an error.
Try with Max
Max({<Year = {$(=Max(Year))}, Month = {"$(=Max({<Year = {$(=Max(Year))}>}Month))"}>} PtileBeg)
Same thing I also tried this and not getting any result: =max({<Month = {$(=Max(Month))}>} PtileBeg)