8 Replies Latest reply: Nov 28, 2016 7:57 PM by Mike Smith RSS

    Very basic set analysis question

    Mike Smith

      Hi Everyone,

       

      I am new to Qlik Sense and have a very straightforward question.

       

      I have watched a number of videos on set analysis and do not understand how you can reference "Year" in the set analysis without stating what date field in the data you are trying to use.

       

      For example, something as simple as Sum({<Year={"2015"}>}Sales)

       

      I, for example, what to look at sum of sales when Booked Date = 2015.

       

      I understand the syntax, but for some reason can't get this to work. All of the examples online seem to have these vague date fields being used which I do not entirely understand.

       

      I know this is very dense of me and I'm likely overthinking this, but any help would be greatly appreciated.

       

      Thanks!

        • Re: Very basic set analysis question
          Agnivesh Kumar

          from where you are getting year field , have you created year from booked date ?

          • Re: Very basic set analysis question
            Stefan Wühl

            "I have watched a number of videos on set analysis and do not understand how you can reference "Year" in the set analysis without stating what date field in the data you are trying to use."

             

            If I understood correctly, you can't. Set analysis is all about selections you apply to fields in your data model, so you need to state the field(s).

             

            Can you point to a video or sample which demonstrates a reference of "Year" in set analysis without stating any field?

             

            (Maybe I just misunderstood the issue. There are different approaches how to filter a "Year" e.g. in a calendar Date field, i.e. you can filter the year in a "Year" field or search all dates of the year in a "Date" field, but you need to state a field from your model)

            • Re: Very basic set analysis question
              Lech Miszkiewicz

              When you execute script ususally you create extra fields from Date like, Month, Year, YearMonth, Day etc..

              Above expression refers actually not to date field but to Year field (column) in data model.

               

              If you have 2 dates in one row it becomes then more difficult.

              You can either create other columns for your Booked Date, like BookedYear, BookedMonth etc.. and then use it like:

              Sum({<BookedYear={"2015"}>}Sales)


              However it then relies not on one but many calendars.

              So the other solution would be to create linking table linked through all different date types with date type description and creating master calendar linked to it.


              I might be able to give you a simple example later.

              regards

              Lech

                • Re: Very basic set analysis question
                  Mike Smith

                  I think I figured out my first issue

                   

                  I am able to bring the date field into an expression as [Booked Date.autoCalendar.Year] which I tested and works for a basic calc like Sum({<[Booked Date.autoCalendar.Year]={"2015"}>}Sales)

                   

                  I have been googling everywhere to figure out how to calculate YTD/QTD/MTD based on the dates in my data (vs. creating a calendar such as what's being done here: The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync)

                   

                  Do you guys have any idea how this can be done? Have been trying for a bit now and can't seem to figure out how to create these date ranges

                   

                  Thanks everyone for the quick responses on this

                    • Re: Very basic set analysis question
                      Lech Miszkiewicz

                      Hi Mike,

                       

                      Well - You have just indicated that you are using Qlik AutoCalendar functionality which sort of deals with dates and formating and i am sure it is useful for Continous scaling etc.

                       

                      However you have no control of how to use fields created in this calendar or at least it would be a way more hassle to explain that to you.

                       

                      In my opinion you should read little bit about scripting, going to script editor and build your own calendar script instead the one which got generated automaticly.

                       

                      Only then you will hava more control on how date fields are linked and how data is transformed. So once you get to script editor place your coursor somewhere at the end of script and type:

                       

                      Calendar:

                      Load Distinct

                           Booked Date,

                           Date(Booked Date) as Date,

                           Month(Booked Date) as Month,

                           Day(Booked Date) as Day

                           Year(Booked Date) as Year

                      Resident

                           (here place table name where Booked Date is kept)

                      ;


                      Then do little more reading about calendars and scripts and YTD,MTD,QTD, MAT and other date groupings.


                      It is not an easy topic for newbe.


                      regards