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
      Year])-1)}>}[Age])

       

       

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

       

       

      regards - joeybird ..please help

        • Re: - 1 year
          Jonathan Poole

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

           

          Untitled.png

          • 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

             

            Joeybird