topic Re: Current Year Values in QlikView

Current Year Values

Mon, 11 May 2015 10:31:12 GMT

Hi All,

I am trying to count only values within the current calendar year and failing. Please could someone point me in the right direction!

I have the following

=count({<DateRaisedIMS={"=year(Today())"},ValueID={'*24*'}, IncidentType={'*Accident*'}, ListTitle={'*Employee*'}>}ValueID)

However this brings back every Value ID in the system

How do I tweak this to just bring back current calendar year figures? I do not want to define a date as it will be part of a dashboard

Thanks in advance

Toby

Re: Current Year Values

Anonymous — Mon, 11 May 2015 10:33:46 GMT

Do you need a dollar expansion ?

=count({<DateRaisedIMS={"$(year(Today()))"},ValueID={'*24*'}, IncidentType={'*Accident*'}, ListTitle={'*Employee*'}>}ValueID)

Re: Current Year Values

sunny_talwar — Mon, 11 May 2015 10:34:31 GMT

Try this:

=count({<DateRaisedIMS={"$(=year(Today()))"},ValueID={'*24*'}, IncidentType={'*Accident*'}, ListTitle={'*Employee*'}>}ValueID)

HTH

Best,

Sunny

Re: Current Year Values

jpenuliar — Mon, 11 May 2015 10:35:14 GMT

Try looking into InYear() function at help (F1)

Re: Current Year Values

Mon, 11 May 2015 10:35:17 GMT

Hi Bill,

I don't know, I should have taken that out as it was used in a previous calculation and have tweaked that to try and get the result I was after

Toby

Re: Current Year Values

Mon, 11 May 2015 10:37:32 GMT

Thanks Sun but unfortunately that did not work

Re: Current Year Values

Mon, 11 May 2015 10:37:43 GMT

Thanks Jonathan I will take a look

Re: Current Year Values

sunny_talwar — Mon, 11 May 2015 10:40:16 GMT

Is DateRaisedIMS a date field???? If yes then you will need to further tweak your formula to be something like this:

=count({<DateRaisedIMS={"=Year(DateRaisedIMS = year(Today())"},ValueID={'*24*'}, IncidentType={'*Accident*'}, ListTitle={'*Employee*'}>}ValueID)

Re: Current Year Values

Mon, 11 May 2015 10:43:39 GMT

Yes DateRaisedIMS is a date field and is the field I am using for all calculations, unfortunately the tweak you made did not work either.

Re: Current Year Values

jpenuliar — Mon, 11 May 2015 10:45:19 GMT

Something like:

If(InYear('01/05/2014',Today(),0),'Y','N') as CurrentYearFlag

you can add this in your Calendar Script, something like:

If(InYear(TempDate,Today(),0),1,0) as CurrentYearFlag,

Re: Current Year Values

sunny_talwar — Mon, 11 May 2015 10:47:03 GMT

Forgot to close the parenthesis after Year(DateRaisedIMS

Try this:

=count({<DateRaisedIMS={"=Year(DateRaisedIMS) = year(Today())"},ValueID={'*24*'}, IncidentType={'*Accident*'}, ListTitle={'*Employee*'}>}ValueID)

Re: Current Year Values

Mon, 11 May 2015 10:47:24 GMT

Can I do it without defining a date range?

I want to make the dashboard 100% self sufficient (as much as possible) without amending the script every year

Thanks

Re: Current Year Values

Mon, 11 May 2015 10:49:07 GMT

Perfect, thank you Sun

You guys are awesome as always, thanks everyone that replied !

Re: Current Year Values

Anonymous — Mon, 11 May 2015 10:49:11 GMT

Toby

Ignoring the rest of your Set Analysis, does this work and juist return this year data ?

count ( {<DateRaisedIMS={">=$(=yearstart(Today()))"}>} ValueID)

If ok then add in the rest of your Set Analysis :

count({<DateRaisedIMS={">=$(=yearstart(Today()))"},ValueID={'*24*'}, IncidentType={'*Accident*'}, ListTitle={'*Employee*'}>}ValueID)

Re: Current Year Values

sunny_talwar — Mon, 11 May 2015 10:50:04 GMT

No Problem

Re: Current Year Values

jpenuliar — Mon, 11 May 2015 10:51:26 GMT

No need to define any dates if you will implement in Script:

You can add this in your Calendar Script, something like:

f(InYear(TempDate,Today(),0),1,0) as CurrentYearFlag,

Also Plenty of Gold Tips at below:

QlikView How To (or Tips & Tricks) Application by Lee Matthews

http://community.qlik.com/docs/DOC-5486

Re: Current Year Values

Mon, 11 May 2015 10:53:32 GMT

Thanks Jonathan, I will take a look at that application!

Toby