Solved: Re: - 1 year - Qlik Community

joeybird · ‎2015-04-07

I have a bar graph

This counts a number of people per month

Count([Age]) this gets me the number of people...and this works fine counting number of people when one year is selected.

By drop down filter pane arrival year…with this behind

Year ([Arrival Date])

I have tried 4! variations of the same code to try and -1
year and have had no joy. e.g I select 2014, and I want it to count 2013.

count({$<Year={$(=Only(Year)-1)}>} [Age])

count({$<Year={$(=Only([Arrival Year])-1)}>}[Age])

count({<[Arrival Year]={$(=max([Arrival
Year])-1)}>}[Age])

count ({$<Year_Fiscal = {'$(vPreviousFiscalYear)'} >}
[Age])

regards - joeybird ..please help

JonnyPoole · ‎2015-04-07

Hi ,

Go to the data load editor and in your load add a new field

Load

[Arrival Date]

Year ([Arrival Date]) as [Arrival Year]

...

from ....

then you [Arrival Year] as the filter pane and also in your set analysis

count({<[Arrival Year]={$(=max([Arrival Year])-1)}>}[Age])

OR.... also suggest trying the new Calendar Tagged fields in Qlik Sense shown here:

Qlik Sense – Date & Time

In your case:

add this at the bottom of the script:

Calendar: DECLARE FIELD DEFINITION TAGGED '$date' Parameters first_month_of_year = 1 Fields

Year($1) As Year Tagged '$year',

Month($1) as Month Tagged '$month',

Date($1) as Date Tagged ('$date', '$day'),

Week($1) as Week Tagged '$week',

Weekday($1) as Weekday Tagged '$weekday',

DayNumberOfYear($1, first_month_of_year) as DayNumberOfYear Tagged ('$numeric');

DERIVE FIELDS FROM FIELDS [Arrival Date] USING Calendar;

and then the date fields will show up in the UI.

The benefit of these are that they sort like dates and behave the way you need Dates to behave. The above sample covers just 6 possible date groupings but there are dozens of dozens of possibilities to create all kinds of date groupings: Quarter-Year , Q-QuarterNumber, Date and Time .....

View solution in original post

JonnyPoole · ‎2015-04-07