Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
hi alex,
i think you have to take ''created date' , since every company use closing date as a reference ...
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
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
Number 1 is easy, just change today() - RowNo() to today() - RowNo() + 31, so you´ll have 31 days from today into your calendar
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.
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
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
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.
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!