topic Max Month in QlikView

Max Month

Fri, 08 May 2015 09:22:26 GMT

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

Re: Max Month

mrossoit — Fri, 08 May 2015 09:25:30 GMT

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

Re: Max Month

Fri, 08 May 2015 09:29:58 GMT

That brings me back 0% ?

Re: Max Month

mrossoit — Fri, 08 May 2015 09:38:59 GMT

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?

Re: Max Month

Anonymous — Fri, 08 May 2015 09:41:04 GMT

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

Re: Max Month

sasikanth — Fri, 08 May 2015 09:45:00 GMT

Try some thing like this

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

Re: Max Month

Fri, 08 May 2015 09:55:08 GMT

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!

Re: Max Month

Anonymous — Fri, 08 May 2015 10:10:55 GMT

Welcome

Re: Max Month

Mon, 11 May 2015 08:43:49 GMT

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.

Re: Max Month

morganaaron — Mon, 11 May 2015 08:54:16 GMT

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!

Re: Max Month

Mon, 11 May 2015 10:00:11 GMT

Hi Aaron

Thanks for your help, I have tried your one of your solutions and it seems to sum all of the months values if it does not find a current month. I like your recommendation but could do with assistance as am very new to this. I have uploaded my example.

In this, I would always like the current month to default i.e 7%, if not then the previous month 2.7% and so on....

However, if you could guide me in improvements to this, I would be extremely grateful.