topic Date Island Problem in QlikView

Date Island Problem

laujerry — Fri, 24 May 2013 06:51:14 GMT

Sorry that this question may be a bit long to read...

I have three tables: SalesHeader, SalesItem and Target

I am going to make an analysis between target & actual sales

SalesHeader

doc date	doc #
1/1/2012	1000101
15/1/2012	1000102
20/1/2012	1000103
1/2/2012	1000104
15/2/2012	1000105
1/3/2012	1000106
1/4/2012	1000107
15/4/2012	1000108
1/5/2012	1000109
15/5/2012	1000110
6/6/2012	1000111

SalesItem

doc #	item #	oty qty
1000101	1	5
1000101	2	3
1000102	1	4
1000103	1	5
1000103	2	7
1000103	3	9
1000104	1	1
1000105	1	6
1000106	1	1
1000106	2	4
1000107	1	1
1000108	1	5
1000109	1	9
1000110	1	3
1000111	1	1

Target

Year	Month	Target
2012	1	5
2012	2	6
2012	3	5
2012	4	7
2012	5	8
2012	6	10

Additionally I loaded

- Year([doc date]) & '-' & Month([doc date]) as [doc yr-mth] // in SalesHeader

- [target year] & '-' & [target month] as [target yr-mth] // in Target

So now I will have SalesHeader and SalesItem naturally joined by QV

and Target as an isolated table

I create a straight table chart using

- [doc yr-mth] // as dimension

- Sum({$<[doc yr-mth]={[target yr-mth}>}target) // as expression1

- Sum([order quantity]) // as expression2

to check my result

I expect the result would be sth like

Yr-Mth	Target	Actual
2012-1	5	33
2012-2	6	7
2012-3	5	5
2012-4	7	6
2012-5	8	12
2012-6	10	1
	41	64

But it shows actually like

Yr-Mth	Target	Actual
2012-1	41	33
2012-2	41	7
2012-3	41	5
2012-4	41	6
2012-5	41	12
2012-6	41	1
	41	64

Remark: the actual tables has lot more fields associated

so I am not going to consider simply making a key joining the 2 yr-mth keys

What's wrong with my expression? Any idea to solve it? Thanks!

Re: Date Island Problem

Gysbert_Wassenaar — Fri, 24 May 2013 07:21:02 GMT

Rename [target yr-mth] to [doc yr-mth], make sure the fields have the same format and use as expression simply sum(Target). See attached example.

Re: Date Island Problem

laujerry — Fri, 24 May 2013 07:40:58 GMT

thanks for your replay

actually my target table will contain other dimension information that associated with the sales data, like "product type", "salesman"... e.g.:

SalesItem

doc #	item #	product type	oty qty
1000101	1	1	5
1000101	2	2	3
1000102	1	3	4
1000103	1	3	5
1000103	2	4	7
1000103	3	5	9
1000104	1	1	1
1000105	1	1	6
1000106	1	1	1
1000106	2	3	4
1000107	1	1	1
1000108	1	1	5
1000109	1	1	9
1000110	1	1	3
1000111	1	1	1

Target

Year	Month	Product type	Target
2012	1	1	3
2012	1	2	2
2012	2	1	4
2012	2	2	2
2012	3	1	5
2012	4	1	7
2012	5	1	8
2012	6	1	10

and the actual table size is very large that using synthetic key will make QV overload

{year-mth / product type}

Re: Date Island Problem

Gysbert_Wassenaar — Fri, 24 May 2013 08:01:09 GMT

Download Qlikview Components and look at the LinkTable.qvw document in the Examples folder. You can create the link table you need with the qlikview components library. The example should show you how to do that.

Re: Date Island Problem

laujerry — Mon, 27 May 2013 08:40:30 GMT

so there is no way other than making use of a key-join .. ?

But i still dont understand why in my case the dimension limit does not work

Yr-Mth	Target	Actual
2012-1	41	33
2012-2	41	7
2012-3	41	5
2012-4	41	6
2012-5	41	12
2012-6	41	1
	41	64