topic Date comparisions in QlikView

Date comparisions

Mon, 05 Dec 2011 12:51:51 GMT

Hi there.

I'm quite new to Qlikview.

I have a basic question.

Let's say this is my database

Month	Year	Period	Value
11	2010	11.2010	35
12	2011	12.2011	38
1	2011	1.2011	50
2	2011	2.2011	33
3	2011	3.2011	34
4	2011	4.2011	48
5	2011	5.2011	47
6	2011	6.2011	43
7	2011	7.2011	30
8	2011	8.2011	41
9	2011	9.2011	42
10	2011	10.2011	35
11	2011	11.2011	32

After import I have a month, year, period the value field.

What I want to do now, is creating a chart, with dates correct ordened, so like this :

How do I do that? because qlikview sorts the row values from 1 to 10, which means that 11.2010 is at the back together with 11.2011

Thanks!

Re: Date comparisions

Miguel_Angel_Baeyens — Mon, 05 Dec 2011 13:14:42 GMT

Hi,

I'd create a field date, which is dual (literal representation, numeric value) so you can sort by this field instead:

Data:

LOAD *, MakeDate(Year, Month) AS Date INLINE [

Month, Year, Period, Value

11, 2010, 11.2010, 35

12, 2011, 12.2011, 38

1, 2011, 1.2011, 50

2, 2011, 2.2011, 33

3, 2011, 3.2011, 34

4, 2011, 4.2011, 48

5, 2011, 5.2011, 47

6, 2011, 6.2011, 43

7, 2011, 7.2011, 30

8, 2011, 8.2011, 41

9, 2011, 9.2011, 42

10, 2011, 10.2011, 35

11, 2011, 11.2011, 32

];

Now, although the chart will look like the same, go to teh chart properties, Sort, and sort Numeric Ascending or Expression, Ascending, Date.

Hope that helps.

Miguel

Date comparisions

Mon, 05 Dec 2011 13:22:04 GMT

Hi there,

I would use the dual()-function similar to this:

Load

. . .

dual(Month(RawDate) & '-' & Year(RawDate), Year(RawDate) & num(Month(RawDate),'00'))

AS Period,

. . .

The dual() creates a numeric/text field "Period" similar to the Month()-function. Now users see the string-representation of the "numeric" field but sorting can be done numerical.

HtH

Roland

Date comparisions

Mon, 05 Dec 2011 13:43:50 GMT

Roland,

Does QlikView recognises Rawdate or do I have to declare it somewhere?

Thanks!

Date comparisions

Mon, 05 Dec 2011 13:53:14 GMT

how stupid of me, now i understand it , sorry Roland

Date comparisions

Mon, 05 Dec 2011 14:03:58 GMT

Hi,

QV doesn't know RawDate as it is a field copied out of one of my load-scripts.

The first parameter of function Dual() is the textual reps of the generated field, second parameter is the internal numeric value.

In your case replace

dual(Month(RawDate) & '-' & Year(RawDate) . . .

with sth like

dual(Period, SubField(Period,'.', 2) & num(SubField(Period,'.', 1))) AS Period,

. . .

Roland

Date comparisions

Mon, 05 Dec 2011 14:09:11 GMT

OK,

working in parallel, I was too late.

And...

meanwhile you managed it for yourself! No Problem.

So go on.

Re: Date comparisions

Anonymous — Mon, 05 Dec 2011 14:35:19 GMT

Hi,

I have attached one example qlikview file for your referrence,May be it will helpful for you to get some idea about qlikview .

Re: Date comparisions

Anonymous — Mon, 05 Dec 2011 14:51:54 GMT

Hi,

Find the attached file may be this solution will be helpful for your doubt...