9 Replies Latest reply: Jun 4, 2018 10:56 AM by J M RSS

    Set analysis with dynamic dates

    J M

      Hi All,


      I know the question f set analysis with dynamic dates has been asked a fair bit, but I cant seem to get my analysis to work. I have a date field called PERIOD which has the format 01.04.2018 (so first of the month and year). This is the same for all of the data in this field. I am then trying to create a table where I can toggle between years and the sum changes. My expression is:


      Sum({$<PERIOD={"$(=Date(01.04.&(Year-1), 'MM/DD/YYYY'))"}>} TOTAL)


      Where Year is the field I want to update, so if you select 2018 it gives you the 2018 dates and if its 2019 then its all the 2019 dates etc etc.


      I cant for the life of me figure out what Im doing wrong. Can anyone help?



        • Re: Set analysis with dynamic dates
          Sunny Talwar

          If you select 2018, it should give all dates from 2018? Why do you need set analysis to do that... QlikView or Qlik sense by default will show you all possible dates from 2018 if you select 2018? Are you may be selecting on Month or date and you don't want them to filter down your expression?

            • Re: Set analysis with dynamic dates
              J M

              Hi Sunny,


              Sorry - I wasnt very clear. Im creating a table and need the calculation in the table to be update when I select a year filter. I have several years to choose from and just want to update it based on the year chosen:


              Account     Apr     May     Jun

              XXX          xx          xx        xx


              So, if I have filtered on Year 2018, I'd like it update the month data to be data for April 2018, May 2018 etc.


              Does that make better sense?

            • Re: Set analysis with dynamic dates
              Sasidhar Parupudi

              May be try


              Sum({$<PERIOD={"$(=Date(Max(PERIOD), 'MM/DD/YYYY'))"}>} TOTAL)

                • Re: Set analysis with dynamic dates
                  J M

                  Hi Sasidhar - that wont work because I need to distinguish between months in the table:


                  Account    Apr    May    Jun

                  XXX          xx          xx        xx


                  which is why I have the formula above becasue I could specify the day and month, but let the year be dynamic (thats the only part that really changes:


                  Sum({$<PERIOD={"$(=Date(01.04.&(Year-1), 'MM/DD/YYYY'))"}>} TOTAL)

                  Fixed for every column in the table

                  Dynamic (based on filter selection)

                • Re: Set analysis with dynamic dates
                  Alexander Cilliers

                  You can define year in load script as well as correctly format the date.

                  • Re: Set analysis with dynamic dates
                    Nicky Maehler

                    Hi Jas,


                    Dates always cause issues. Just watch out for the date being DD.MM&(Year) or MM.DD&(Year) compared to 'MM/DD/YYYY'.

                    I find it is easiest to put the value you want within the Set Analysis into a variable and rather use that.

                    vMaxYear = max(Year);


                    vMaxMonthYear = MonthStart(max(Date));

                    The set analysis will be something like:

                    Sum({$<PERIOD={"$(=Date(01.04.&$(vMaxYear), 'MM/DD/YYYY'))"}>} TOTAL)


                    Sum({$<PERIOD={'$(vMaxMonthYear)'}>} TOTAL)

                    Option one can get a bit "confused". Try hard coding the value to ensure that the dates are being interpreted correctly.

                    i.e. build the set analysis up slowly to work out where you are going wrong.

                    Also check how your dates are set on your main tab - this can be conflicting.

                    When I have issues with dates I often use DayName(PERIOD) rather than Date(PERIOD, 'MM/DD/YYYY')