A few questions related to the year to date analysis

[#1]

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

[preminqlik]

hi alex,

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

[#3]

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

[preminqlik]

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

[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

[chriscammers]

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.

[#7]

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

[#8]

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

[Carlos_Reyes · Accepted solution]

It's because of the Number of records you set in the AUTOGENERATE. Try:

LOAD

  RowNo() as RowID1,

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

  date(today()-RowNo()+31) as Date,

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

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

AUTOGENERATE 1

While (Num(Today()-IterNo()+31))>= (Num(YearStart(AddYears(Today(),-2))));

This will generate all dates from the 1 of January ( two years ago ) to 31 days after today.

[#10]

Indeed Carlos, you were correct!

I realized this as well a few days later after I posted the question and came with a temporary solution, however yours fits like a glove

Thank you!