9 Replies Latest reply: Nov 14, 2013 10:49 AM by A A RSS

    A few questions related to the year to date analysis

      Hello to everyone,

       

      I have one question related to the year to date analysis:

       

      Assuming the following example, in which I have an sql data base with the date an order has been placed (created) and the date when the order has been completed (closed)

       

      Table1:

      LOAD

          `created_date`,

          `closed_date`,

          SQL SELECT *

      FROM e.`e_i`;

       

      In most examples I saw everyone was generating the date based on the date existing in the database already (eg. created date). In my case however, there are 2 dates (closed_date as well) and my analysis needs to embody both of them.

      As such, I though of creating an universal date, to which I am comparing the ones in the data base:

       

      Data:

      LOAD

        RowNo() as RowID1,

        date(today()-RowNo(), 'MMM YYYY') as Date1,

        Month(today() - RowNo()) as Month1,

        Year(today() - RowNo()) as Year1

      AUTOGENERATE(1000);

       

      I have 2 problems with this solution:

       

      1. It is not up to date (Date1 will consider all dates up to October 2013 and I need it to generate the dates for the current month as well: November).


      2. Assuming that I need to analyse a set of orders from the 10th Jan 2010 (I was searching after the order number actually, 440, which had the created date 10th Jan 2010):


      When I list the years and months, at 2010 it will exclude Jan totally and start the analysis from Feb (It will list Jan, but show no data for it, even if there are orders placed in Jan after the 10th)

       

      Could anyone help me with this? Needless to say that I started learning Qlikview 1 month ago ..

       

      Thank you,

      Alex


        • Re: A few questions related to the year to date analysis
          Prem Kumar Thangallapally

          hi alex,

          i think you have to take ''created date' , since every company use closing date as a reference ...

            • Re: A few questions related to the year to date analysis

              Thank you Prem for your answer.

               

              I've tried actually with this formula however it is not calculating properly as the closed date is not a reference of the opened one; actually both dates are a reference of the order number itself. Also, I have months when orders have been closed only (none were open), and orders which were open in one month and closed 2-3 months later, or next year (thus, the month and year of the closed date is not the same as the month and year of the created one for an order as in)

               

              Also, let's assume that I want to calculate in a chart the no. of orders open and the no. of orders closed per month (I can easily do that in 2 charts, using as different dimensions the created_date and closed_date and counting the orders, but how can I do this in one chart?).

               

              Thank you,

              Alex

                • Re: A few questions related to the year to date analysis
                  Prem Kumar Thangallapally

                  hi again,

                  according to my understanding you have to concatenate the same table with giving flag as following :

                   

                   

                  tab:

                  load CustomerID,

                            OpenedDate                       as                         Date,

                            Orders,

                             'Opened'                              as                         flag

                  from orginaltable;

                  concatenate

                  load CustomerID,

                       ClosedDate                              as                         Date,

                       Orders,

                       'Closed'                                       as                         flag

                  from orginaltable;

                   

                  and now link it with master calendar

                   

                   

                   

                  //**********************************

                  now in UI  write the below expressions

                   

                  take dimension as Customer Id,(Year or Month)

                  expression1 (Counting  opened orders) = count({<flag={'Opened'}>}Orders)

                  expression2 (Counting  Closed orders) = count({<flag={'Closed'}>}Orders)

                   

                   

                  let me know

                   

                   

                   

                  Regards

                  premhas

              • Re: A few questions related to the year to date analysis
                Clever Anjos

                Number 1 is easy, just change today() - RowNo() to today() - RowNo() + 31, so you´ll have 31 days from today into your calendar

                • Re: A few questions related to the year to date analysis
                  Chris Cammers

                  Here is what I would suggest.

                  You have to think of your order in terms of events and what things you want to measure at each event then specify a single date based on the appropriate event date.

                   

                  You will load "orders created" into a table.

                  OrderFact:

                  Load

                       create_date as MASTER_DATE,

                       'Orders Created' as FactType, //this is an optional field that I think helps you track down problems in expressions

                       Amount as OrderedAmount

                  From Table

                   

                  Next you will concatenate the same rows using the Closed date as the Master date and using appropriate nameing for the the measure fields

                  Concatenate(OrderFact)

                  Load

                       closed_date as MASTER_DATE,

                       'Orders Closed' as FactType, //this is an optional field that I think helps you track down problems in expressions

                       Amount as ShippedAmount

                  From Table

                   

                  Finally add your calendar using MASTER_DATE as the date key.

                   

                  When you make selections on the calendar Qlikivew will sum the rows created and closed during the period in the same chart.

                  • Re: A few questions related to the year to date analysis

                    Thank you all for your answers.

                     

                    I tried concatenating the 2 values, actually did that before also, however some of them are actually equal, thus the calculus will exclude these.

                    Therefore I need an overall Master Date to be used as reference (from all the test I've done, the moment I generate it from the Open Tickets, the closed ones will be calculated wrong)

                     

                    I added 31 to RowNo() and indeed it extended the values to the current month as well, thus this worked!

                    Thank you Clever!

                     

                    Now my Data Table looks like this:

                     

                    Data:

                    LOAD

                      RowNo() as RowID1,

                      date(today()-RowNo()+31, 'MMM YYYY') as Date1,

                      Month(today() - RowNo()) as Month1,

                      Year(today() - RowNo()) as Year1

                    AUTOGENERATE(1000);

                     

                    While it is calculating very well, it starts the date from April 2011. I need it to start from April 2010 (one year before as in)

                     

                    Any thoughts?

                     

                    Thank you all once again,

                    Alex

                    • Re: A few questions related to the year to date analysis

                      Hello again and truly sorry to bother you all.

                       

                      I am in a total fix with this issue as I have no clue why it is generating the data from April 2011.

                       

                      Does anyone know how to make it start from April 2010? Please help