Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

joeybird
Contributor II

- 1 year

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

1 Solution

Accepted Solutions
Employee
Employee

Re: - 1 year

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 &amp; 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 .....

Untitled.png

4 Replies
Employee
Employee

Re: - 1 year

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 &amp; 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 .....

Untitled.png

joeybird
Contributor II

Re: - 1 year

Fantastic..all working xx

Thank you.

Calendar date and time fields really useful too... saves setting up dimensions

Kind Regards

Joeybird

Employee
Employee

Re: - 1 year

Super!  Please mark this one as 'answered' with the correct answer marked. Cheers

joeybird
Contributor II

Re: - 1 year

completed x

Kind Regards

Joeybird