26 Replies Latest reply: Feb 22, 2017 7:35 PM by Richard Judkins RSS

    Year/month dimensions

    Richard Judkins

      Hi all

       

      This may get quite convoluted pretty quickly, so bear with me!

       

      Ok, so I'm using Qlik Sense Server.

       

      I have a data connection to quite a large table of data, which is basically client agreements dating back to 2003, and these agreements are for a variety of different products.

       

      The table contains a huge amount of data (over 70,000 agreement details), but the columns I'm specifically talking about in this situation are 'EFF_START_DT', which is the date the agreement started, and 'EFF_END_DT', which, for agreements that have expired/been cancelled etc., the date the agreement ended.

       

      I am building an app which I want to show, by month/year, the number of agreements that ended that month (either showing a full year by month, or select filter on selected months), and the base of agreements still active as of that month.

       

      Now, I have managed to get it working for the Ended subs view, which was relatively simple, as I just made the date dimensions (month and year) based on the EFF_END_DT, (month(EFF_END_DT)), and counting the numbers of subs that ended that year/month, (count(AGREE_NUM)).

       

      However, the issue I have now is I can't for the life of me work out how to incorporate the total number of subs still active as of that same month/year i.e. the EFF_END_DT is null.

      Even if I have a completely different App for the base numbers of subs, how do I script something that beings back the number of active subs as of (for example) February 2017, or a full year view of 2016 showing the base growth month by month?

      I really don't want to have a separate app though, which adds a whole lot of complexity.

       

      Here is an example of the background script in the data load editor (it's greatly abridged - I'm selecting dozens of columns and have a lot of case statements to group products etc)

       

      Select

      AGREE_ID
      ,AGREE_NUM
      ,EFF_START_DT
      ,EFF_END_DT
      ,AGREE_STATUS


      from
      CX_LKP_RECURRING_AGREEMENTS_BASE


      where EFF_START_DT <= getdate()
      and year(EFF_START_DT) >= 2003

       

      Thanks!

        • Re: Year/month dimensions
          Aar Kay

          Can you please post some sample data

            • Re: Year/month dimensions
              Richard Judkins

                 

              AGREE_IDAGREE_NUMAGREE_STATUSEFF_START_DTEFF_END_DTBU_IDAGREEMENT_PRODUCT_GROUP
              356001SUBS5093124Expired16/09/200731/08/20091-14J-1Basics
              2355889SUBS5093121Expired1/03/200713/02/20171-14J-1Basics
              2111177SUBS5093540Expired1/05/20081/06/20161-14J-1Standard
              223358SUBS5090633Active1/03/2007NULL1-14J-1Basics
              211111SUBS5090576Cancelled13/06/20091/06/20111-14J-1Standard
              698889SUBS5093108Active1/03/2007NULL1-14J-1Basics
              56689777SUBS5090571Expired1/03/201030/04/20111-14J-1Basics
              11554487SUBS5090606Expired16/03/200714/07/20081-14J-1Standard
              32566699SUBS5090566Cancelled1/12/20087/01/20091-14J-1Basics
              8755789SUBS5091058Active1/03/2007NULL1-14J-1Standard
                • Re: Year/month dimensions
                  Sunny Talwar

                  I may not have fully understood the issue, but it seems that you might need to use IntervalMatch between your start and end date and a master calendar field. Based on the data provided, what is the expected output you are hoping to get?

                    • Re: Year/month dimensions
                      Richard Judkins

                      Ok, based on the data I provided, I would be hoping to get, in a table (or pivot-table) and bar chart:

                       

                      If February 2017 is selected in the filter pane, and no product selected, it would show 1 agreement ended, and a base of 3 active agreements.

                      If the same date selected, but 'Basics' selected as the product, still 1 agreement ended, with a base of 2 active agreements.

                      No month selected, but 2011 only, the table and bar graph showing 1 agreement ending in April and 1 in June, with the base showing 7 active agreements for each month Jan-Mar, 6 for May, and 5 for each month for the rest of the year.

                       

                      Hope that makes sense!

                        • Re: Year/month dimensions
                          Sunny Talwar

                          See if this looks like something you want

                           

                          Capture.PNG

                           

                          Script (Coded in QlikView, but should be same for Qlik Sense)

                           

                          Table:

                          LOAD AGREE_ID,

                               AGREE_NUM,

                               AGREE_STATUS,

                               EFF_START_DT,

                               EFF_END_DT,

                               Date(If(EFF_END_DT = 'NULL', Today(), EFF_END_DT)) as [End Date],

                               If(EFF_END_DT = 'NULL', MonthStart(Today(), 1), MonthStart(EFF_END_DT)) as EndCheck,

                               BU_ID,

                               AGREEMENT_PRODUCT_GROUP

                          FROM

                          [https://community.qlik.com/thread/250638]

                          (html, codepage is 1252, embedded labels, table is @1);

                           

                          Calendar:

                          LOAD Date(MinDate + IterNo() - 1) as Date,

                            Date(MonthStart(MinDate + IterNo() - 1), 'MMM-YYYY') as MonthName

                          While MinDate + IterNo() - 1 <= Today();

                          LOAD Min(EFF_START_DT) as MinDate

                          Resident Table;

                           

                          IntervalMatch:

                          IntervalMatch(Date)

                          LOAD EFF_START_DT,

                               [End Date]

                          Resident Table;

                            • Re: Year/month dimensions
                              Richard Judkins

                              Kind of yes, except it will only be a single year view (or a single month if selected).

                               

                              Imagine the Qlik Sense version of this (with the little boxes at the top showing Month/Ended/Base/% Ended:

                               

                               

                               

                               

                               

                              And sorry - not sure where your script would go? What does "Table:" "Calendar:" and "Resident Table;" refer to please?

                               

                              And I can't add an "End Check" to the data table sorry - I don't have control over the table at all - the columns are as is.

                                • Re: Year/month dimensions
                                  Sunny Talwar

                                  See if the attached helps

                                   

                                  Capture.PNG

                                    • Re: Year/month dimensions
                                      Richard Judkins

                                      Time for a real numpty question sorry - how do I view a QVF file?

                                       

                                      I don't have access to the QMC, btw.

                                       

                                      And something else I should mention sorry. I need to be able to select the Year and Month independently.

                                      For example, choose 2016, with no month selected, to show all 12 months.

                                      Or, 2016 and June, to show just June 2016.

                                        • Re: Year/month dimensions
                                          Sunny Talwar

                                          Do you have Qlik Sense desktop installed on your machine? If you do then drag the file onto a Qlik Sense desktop to open the qvf.

                                           

                                          For filtering on Month and Year, you can create Month and Year filters for making selections

                                            • Re: Year/month dimensions
                                              Richard Judkins

                                              I do, and it used to work, but I can't get it to work now. I have repaired, and even uninstalled/reinstalled it, but it just starts up with a blank white window and does nothing else.

                                              Capture.JPG

                                                • Re: Year/month dimensions
                                                  Sunny Talwar

                                                  May be download and install the most recent version of Qlik Sense and then try

                                                    • Re: Year/month dimensions
                                                      Richard Judkins

                                                      Thank you Sunny - that worked.

                                                       

                                                      Really sorry to be a pain, but a couple of things.

                                                       

                                                      As I said earlier, I can't add any columns to the table data (I have no control over the source tables, they are what they are) - so I can't add an 'EndCheck' column. How does that change the script?

                                                       

                                                      And the format/syntax of the script is quite different than what I'm used to - I use SELECT instead of LOAD, for example, and I'm not sure what headings such as 'Calendar" do?

                                                       

                                                      Does it matter, in the end though?

                                                        • Re: Year/month dimensions
                                                          Sunny Talwar

                                                          1) Not asking you to modify the source data to add another column. This would be done in the QlikView script using the existing fields.

                                                           

                                                          Table:

                                                          LOAD AGREE_ID,

                                                              AGREE_NUM,

                                                              AGREE_STATUS,

                                                              EFF_START_DT,

                                                              EFF_END_DT,

                                                              Date(If(EFF_END_DT = 'NULL', Today(), EFF_END_DT)) as [End Date],

                                                              If(EFF_END_DT = 'NULL', MonthStart(Today(), 1), MonthStart(EFF_END_DT)) as EndCheck,

                                                              BU_ID,

                                                              AGREEMENT_PRODUCT_GROUP

                                                          FROM

                                                          [https://community.qlik.com/thread/250638]

                                                          (html, codepage is 1252, embedded labels, table is @1);

                                                           

                                                          If you see above, I am using EFF_END_DT to create a new field EndCheck just like you would do in SQL Select statement

                                                           

                                                          2) LOAD is select equivalent of SELECT. Calendar is the name of the table, you can give this any name you would feel comfortable with.

                                                            • Re: Year/month dimensions
                                                              Richard Judkins

                                                              Ah, I see, I got it backwards. Thank you, still learning the SQL ropes!

                                                               

                                                              So, here's my script as it is now:

                                                               

                                                              LIB CONNECT TO 'SQL OLAP';
                                                              [Agreement Summary]:
                                                              SQL

                                                               

                                                              LOAD
                                                                  AGREE_ID,
                                                                  AGREE_NUM,
                                                                  AGREE_STATUS,
                                                                  EFF_START_DT,
                                                                  EFF_END_DT,
                                                                  Date(If(EFF_END_DT = 'NULL', Today(), EFF_END_DT)) as [End Date],
                                                                  If(EFF_END_DT = 'NULL', MonthStart(Today(), 1), MonthStart(EFF_END_DT)) as EndCheck,
                                                                  BU_ID,
                                                                  AGREEMENT_PRODUCT_GROUP

                                                               

                                                              FROM
                                                              CX_LKP_RECURRING_AGREEMENTS_BASE

                                                               

                                                              Calendar:
                                                              LOAD Date(MinDate + IterNo() - 1) as Date,
                                                                Date(MonthStart(MinDate + IterNo() - 1), 'MMM-YYYY') as MonthName
                                                              While MinDate + IterNo() - 1 <= Today();
                                                              LOAD Min(EFF_START_DT) as MinDate
                                                              Resident Table;

                                                              IntervalMatch:
                                                              IntervalMatch(Date)
                                                              LOAD EFF_START_DT,
                                                                   [End Date]
                                                              Resident Table;

                                                               

                                                              But I'm getting an 'Incorrect syntax near the keyword 'If'' error...

                                                                • Re: Year/month dimensions
                                                                  Sunny Talwar

                                                                  What is the exact error you are getting? Can you share a screenshot?

                                                                      • Re: Year/month dimensions
                                                                        Sunny Talwar

                                                                        You might want to do like this

                                                                         

                                                                        LIB CONNECT TO 'SQL OLAP';
                                                                        [Agreement Summary]:

                                                                        LOAD
                                                                            AGREE_ID,
                                                                            AGREE_NUM,
                                                                            AGREE_STATUS,
                                                                            EFF_START_DT,
                                                                            EFF_END_DT,
                                                                            Date(If(EFF_END_DT = 'NULL', Today(), EFF_END_DT)) as [End Date],
                                                                            If(EFF_END_DT = 'NULL', MonthStart(Today(), 1), MonthStart(EFF_END_DT)) as EndCheck,
                                                                            BU_ID,
                                                                            AGREEMENT_PRODUCT_GROUP;

                                                                        SQL SELECT

                                                                             AGREE_ID,

                                                                             AGREE_NUM,

                                                                             AGREE_STATUS,

                                                                             EFF_START_DT,

                                                                             EFF_END_DT,

                                                                             BU_ID,

                                                                             AGREEMENT_PRODUCT_GROUP

                                                                        FROM CX_LKP_RECURRING_AGREEMENTS_BASE;

                                                                         

                                                                        This way of loading data is called Preceding Load where you pull data from SQL and on top do a QlikView load. You don't really need a qlikview load unless you want to do some manipulation.

                                                                          • Re: Year/month dimensions
                                                                            Richard Judkins

                                                                            Sigh.

                                                                             

                                                                              • Re: Year/month dimensions
                                                                                Sunny Talwar

                                                                                You named your table to be Agreement Summary, so resident load will be from Agreement Summary and not Table

                                                                                 

                                                                                Calendar:

                                                                                LOAD Date(MinDate + IterNo() - 1) as Date,

                                                                                  Date(MonthStart(MinDate + IterNo() - 1), 'MMM-YYYY') as MonthName

                                                                                While MinDate + IterNo() - 1 <= Today();

                                                                                LOAD Min(EFF_START_DT) as MinDate

                                                                                Resident [Agreement Summary];


                                                                                IntervalMatch:
                                                                                IntervalMatch(Date)
                                                                                LOAD EFF_START_DT,
                                                                                    [End Date]
                                                                                Resident [Agreement Summary];

                                                                                  • Re: Year/month dimensions
                                                                                    Richard Judkins

                                                                                    Thank you Sunny - I genuinely hugely appreciate the help.

                                                                                     

                                                                                    I've only just recently started this role at work, and have taken over the development of all the company's Qlik reporting from the guy who built all the existing reporting, who has since left the company.

                                                                                     

                                                                                    And I have had zero prior experience in SQL and Qlik, so learning as I go!

                                                                                     

                                                                                    I'll give this a go (after adding all the bits to the script I took out for the purposes of this discussion) and come back if there's something catastrophic!

                                                                                      • Re: Year/month dimensions
                                                                                        Sunny Talwar

                                                                                        Sounds like a plan

                                                                                          • Re: Year/month dimensions
                                                                                            Richard Judkins

                                                                                            Ok, worked without errors (huzzah!)

                                                                                             

                                                                                            However, I have a couple of case/when's included.

                                                                                             

                                                                                            Here is one of them:

                                                                                            Capture.JPG

                                                                                             

                                                                                            What's happening is the new 'DIRECT_SUB_CHANNEL' is now no longer being created (the same for the other section where I'm grouping product groups).

                                                                                              • Re: Year/month dimensions
                                                                                                Sunny Talwar

                                                                                                Where was this initially?

                                                                                                  • Re: Year/month dimensions
                                                                                                    Richard Judkins

                                                                                                    It was in the "Select" section (I had no "Load" section, of course).

                                                                                                     

                                                                                                    Should I be moving it to the Load section, or having them in both the Load and Select sections?

                                                                                                     

                                                                                                    Edit: Ok, the data load fails if I move it to the Load section and if I have it in both Load and Select.

                                                                                                    So, in a nutshell, the data load works ok if it's just in the Select section, but, well, the case section doesn't actually do anything.

                                                                                                     

                                                                                                    Also: here is the current visualisation (with the raw product names - the total numbers should be accurate regardless).

                                                                                                     

                                                                                                    Capture.JPG

                                                                                                     

                                                                                                    The "Ended Subs" figure is dead on.

                                                                                                     

                                                                                                    However, the "Base" number should be around 30,000 (and growing slightly each month).

                                                                                                     

                                                                                                    Here is the 2016 view - as you can see it's going in completely the wrong direction, at a great rate:

                                                                                                     

                                                                                                    Capture.JPG

                                                                                                    January should be around 23,000, increasing every month to be around 30,000 in December:

                                                                                                     

                                                                                                     

                                                                                                    My Dimensions:

                                                                                                    Month - month(MonthName)

                                                                                                    Year - year(Date)

                                                                                                     

                                                                                                    Measures:

                                                                                                    Ended subs - Count(DISTINCT Aggr(If(MonthName = EndCheck, AGREE_NUM), MonthName, AGREE_NUM))

                                                                                                    Base - Count(DISTINCT Aggr(If(MonthName < EndCheck, AGREE_NUM), MonthName, AGREE_NUM))

                                                                                                     

                                                                                                    It's obviously something to do with the EndCheck, but I can't work it out sorry.

                                                                                                     

                                                                                                    And...is it normal to get "Calculation time out' errors which prevent visualisations opening? I've been getting a lot of them today using this new scripting.

                                                                                                      • Re: Year/month dimensions
                                                                                                        Sunny Talwar

                                                                                                        I am a little lost now, you will have to break down your questions for me. I am not sure what happened with your case statement. Is it not showing up on the front end? Would you be able to share your complete script or ideally your app to see what you trying to do

                                                                                                          • Re: Year/month dimensions
                                                                                                            Richard Judkins

                                                                                                            Fair enough - attached is the whole script. I'm not sure I can share the app - I don't have access to the server, so can't get a copy.

                                                                                                             

                                                                                                            In a nutshell, the issues I have are:

                                                                                                             

                                                                                                            • The case statements - sorted. Changed the Load to load PROD_GROUP instead of AGREEMENT_PRODUCT_GROUP etc
                                                                                                            • The Base is not calculating correctly - should be around 30,000 each month an increasing slightly each month
                                                                                                            • I keep getting 'Calculation Time out' errors when the app tries to load the visualisations - leaving just a red box (similar to when there's an invalid dimension). Could it maybe have something to do with this:

                                                                                                             

                                                                                                            Capture.JPG

                                                                                                            Capture.JPG

                                                                                                             

                                                                                                            Nearly 20 million lines being fetched is a hell of a lot - my old script only fetched around 60,000 (which is the fist "Lines fetched" figure above).

                                                                                                             

                                                                                                            Or is this not even the case - because of the Calendar is it in fact only fetching 3,657 lines?

                                                                                                             

                                                                                                            Whatever it is, when I commented out the Calendar and Interval sections there was no issue at all with them loading. Loaded with no data, of course, but...

                                                                                                             

                                                                                                            Thanks again for your help Sunny - I realise it's probably turned into a bit of a quagmire, so I wont be offended at all if you decide to bail!