calender

neha_sri — Sat, 20 Sep 2014 10:34:13 GMT

Hi,

Actual I want to create a single calender,but I have 2 dates.

1.Order date

2.Hire date

I want to keep a single calender which consist both the dates and their corresponding months and dates.How to do so..

Thanks in anticipation

Re: calender

marcus_sommer — Sat, 20 Sep 2014 10:47:08 GMT

See here: http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

- Marcus

Re: calender

MK_QSL — Sat, 20 Sep 2014 12:15:43 GMT

You need to use Cannonical Date... Let me give you one example..

Consider that you have Sales Data where there are two dates... OrderDate and DeliveryDate....

Now requirement is to find Month wise TotalValue for Order Received and Order Delivered...

Use below script ...

===============================

Sales:

Load * Inline

[

OrderID, Customer, OrderDate, DeliveryDate, Price

ORD001, A, 05/01/2014, 10/01/2014, 120

ORD002, B, 07/01/2014, 12/01/2014, 100

ORD003, A, 16/01/2014, 20/01/2014, 75

ORD004, C, 25/01/2014, 01/02/2014, 250

ORD005, D, 28/01/2014, 05/02/2014, 80

ORD006, B, 05/02/2014, 15/02/2014, 125

ORD007, B, 18/02/2014, 20/02/2014, 170

ORD008, C, 25/02/2014, 28/02/2014, 100

ORD009, B, 01/03/2014, 10/03/2014, 90

ORD010, A, 04/03/2014, 11/03/2014, 80

ORD011, D, 15/03/2014, 28/03/2014, 200

ORD012, C, 20/03/2014, 01/04/2014, 240

ORD013, A, 25/03/2014, 05/04/2014, 210

ORD014, E, 28/03/2014, 18/04/2014, 340

ORD015, B, 01/04/2014, 10/04/2014, 70

ORD016, D, 11/04/2014, 15/04/2014, 60

ORD017, E, 15/04/2014, 20/04/2014, 50

ORD018, C, 18/04/2014, 22/04/2014, 40

ORD019, B, 23/04/2014, 28/04/2014, 140

ORD020, E, 25/04/2014, 30/04/2014, 100

];

Mapping_OrderDate:

Mapping Load

OrderID,

OrderDate

Resident Sales;

Mapping_DeliveryDate:

Mapping Load

OrderID,

DeliveryDate

Resident Sales;

DataBridge:

Load

OrderID,

ApplyMap('Mapping_OrderDate',OrderID) as CannonicalDate,

'Order' as DataType

Resident Sales;

Load

OrderID,

ApplyMap('Mapping_DeliveryDate',OrderID) as CannonicalDate,

'Delivery' as DataType

Resident Sales;

Join

Load CannonicalDate, Month(CannonicalDate) as CannonicalMonth Resident DataBridge;

===============================

Now create a Straight Table

Dimension

CannonicalMonth

Expression

SUM({<DataType = {'Order'}>}Price)

SUM({<DataType = {'Delivery'}>}Price)

Hope this helps...

HAPPY QLIKING !!

Re: calender

ecolomer — Sat, 20 Sep 2014 16:18:53 GMT

Here you have an example

OR ... Combine TWO calendars in ONE:

Table1:

Load

Date1,

Date1 as PK_Date

From Table1

;

Table2:

Load

Date2,

Date2 as PK_Date

From Table2

;

LinkTable:

Load DISTINCT

PK_Date,

Date1 as Date

Resident Table1

;

Load DISTINCT

PK_Date,

Date2 as Date

Resident Table2

;

topic Re: calender in QlikView

calender

Re: calender

Re: calender

Re: calender