Max Month

[#1]

Please could someone assist me with the max month function - or alternative!

My data looks like this:

SicknessMonth	TargetSickness	ActualSickness
January	3%	1.7%
February	3%	3.5%
March	3%	3.8%
April	3%	2.7%
May	3%
June	3%
July	3%
August	3%
September	3%
October	3%
November	3%
December	3%

My current expression in my guage is =sum(if (SicknessMonth = 'January',  ActualSickness))

This correctly delivers 1.7

However I always want my chart to show the current month - which should be the last entry......

Thanks

Extra attachment for further assistance added 11.05.2015

[mrossoit]

=sum(if (lower(left(SicknessMonth, 3)) = month(today()),  ActualSickness))

[#3]

That brings me back 0% ?

[mrossoit]

I corrected the expression adding lower() function. Using today() function, current month returned will be may. If your data are limited to which I see in your original post, there are no values in may.

Have you to consider current month or last month having data associated?

[Anonymous · Accepted solution]

Hi

You dont have value for current month that is May .

If you always want to show the current month value you can use below QVW

If your requirement is something else let me know .

PS: I added one value for may month

Thanks

BKC

[sasikanth]

hi

Try some thing like this

sum(if(Not isNull(ActualSickness),ActualSickness,TargetSickness)

[#7]

Excellent!!  Thank you for your help.

I did try it with a value in May but it was the mapping to number that I needed.

Thanks again!

[Anonymous]

Welcome

[#9]

BKC

Thanks for your help in making my guage fix to the current month

Would there be anyway of over-riding this by selecting another month from a selection box.

I.e leave the default to the current but  have the ability to change to a previous selection.

[morganaaron]

Hi Emma,

You can use various things here, a simple fix would be:

=If(GetSelectedCount(SicknessMonth)>0, Sum(ActualSickness), sum(if(Month=Month(Today()),ActualSickness)))  if you only want them to make a selection in the "Sickness Month" field or..

=If(GetPossibleCount(SicknessMonth)>0, Sum(ActualSickness), sum(if(Month=Month(Today()),ActualSickness))) if they can be making selections anywhere and if there's only one month available you want it to show that month.

I'd recommend considering your data model however - do you need future months in your model? I'd load in a proper calendar and only load up to the current month, that way you can avoid any of the above and just use a set using the max month - which will by default be the latest month or whenever a month is selected, will be that month. Shout if you need a hand looking over your model to do so!