topic Re: Combine 2 Calculated Date Dimensions in QlikView

Combine 2 Calculated Date Dimensions

Anonymous — Tue, 22 Dec 2015 15:39:22 GMT

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!

Re: Combine 2 Calculated Date Dimensions

Wed, 23 Dec 2015 08:16:56 GMT

hi Maarten,

could you please sample data and what output you need

Regards

Sachin

Re: Combine 2 Calculated Date Dimensions

Anonymous — Thu, 24 Mar 2016 13:41:12 GMT

I no longer need this.

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

Re: Combine 2 Calculated Date Dimensions

amayuresh — Mon, 28 Mar 2016 10:11:07 GMT

Do you required output like below:

Used Data:

Script:

Re: Combine 2 Calculated Date Dimensions

maxgro — Mon, 28 Mar 2016 10:20:09 GMT

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

Canonical Date

Re: Combine 2 Calculated Date Dimensions

tyagishaila — Mon, 28 Mar 2016 10:34:54 GMT

you need to create common Date Field.as

topic Re: Combine 2 Calculated Date Dimensions in QlikView

Combine 2 Calculated Date Dimensions

Re: Combine 2 Calculated Date Dimensions

Re: Combine 2 Calculated Date Dimensions

Re: Combine 2 Calculated Date Dimensions

Re: Combine 2 Calculated Date Dimensions

Re: Combine 2 Calculated Date Dimensions

Mayuresh Athalye and maxgro

suggest.