4 Replies Latest reply: Apr 8, 2015 6:22 AM by Joanna Seldon RSS

    - 1 year

    Joanna Seldon

      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



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



      regards - joeybird ..please help

        • Re: - 1 year
          Jonathan Poole

          Hi ,


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



               [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 .....



          • Re: - 1 year
            Joanna Seldon



            Fantastic..all working xx


            Thank you.


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


            Kind Regards