Combine 2 Calculated Date Dimensions

[Anonymous]

Hi all,

I have  2 questions relating the following date:

Descriptions of values:

work_status = Location of work

receipt_date = Receipt of goods

status_date = Date at which status changed

I am looking for a single graph on which I can plot the count of goods received (Rec.Count) and good completed (Comp.Count) against dates.

I have 2 calculated date dimensions as follows:

Rec.Date =aggr(date(only({< work_status = , receipt_date = {"$(='>=' & vStartDate_Target & '<=' & vEndDate_Target)"} >} receipt_date )), work_id)

Comp.Date =aggr(date(only({< work_status = {'COMP'}, status_date = {"$(='>=' & vStartDate_Target & '<=' & vEndDate_Target)"} >} status_date work_id)

With the following Expressions:

Rec.Count =count({< work_status = , receipt_date = {"$(='>=' & vStartDate_Target & '<=' & vEndDate_Target)"} >} work_id)

Comp.Count = =count({< work_status  = {'COMP'}, status_date = {"$(='>=' & vStartDate_Target & '<=' & vEndDate_Target)"} >} work_id)

I got the following table from the formulas above:

Rec.Date	Comp.Date	Rec.Count	Comp.Count
		20	6
19/12/2015		7	0
20/12/2015		10	0
15/12/2015		13	0
17/12/2015		16	0
16/12/2015		19	0
18/12/2015		20	0
14/12/2015		20	0
	14/12/2015	0	1
	17/12/2015	0	1
	17/12/2015	0	1
	15/12/2015	0	1
	15/12/2015	0	1
	15/12/2015	0	1

2 Questions:

1) How do I get both Rec.Date and Comp.Date merged as a single dimension?

2) Why in the straigt table view would I not get the accumulation of the Comp.Count?

Many thanks for you help!

[#2]

hi Maarten,

could you please sample data and what output you need

Regards

Sachin

[Anonymous]

I no longer need this.

I could not add further details as I have limited licence on Qlikview

[amayuresh · Accepted solution]

Do you required output like below:

Used Data:

Script:

[maxgro]

Maybe you can add a new date field (from receipt_date and status_date) as explained here

Canonical Date

[tyagishaila]

you need to create common Date Field.as

Mayuresh Athalye and maxgro

suggest.