topic Re: Handling Multiple Dates in a Single Report in QlikView

Handling Multiple Dates in a Single Report

Tue, 17 Feb 2015 07:57:18 GMT

Hi Guys,

I am having 4 Fact Tables and 4 different dates coming from the same. The developed report consists of Measures dependent on different Fact Tables. whenever I Select a Date from the respective Fact table, only the data associated to it shows correct values.

I need a single date which would be associated with all the Fact tables.

Kindly help me to resolve the issue.

Thanks & Regards

Sumeet

Re: Handling Multiple Dates in a Single Report

jagan — Tue, 17 Feb 2015 08:10:38 GMT

Hi Sumeet,

Check this link for solution

Canonical Date

Regards,

Jagan.m

Re: Handling Multiple Dates in a Single Report

tobias_klett — Tue, 17 Feb 2015 08:12:25 GMT

Hi Sumeet,

check my Document on a Calendar Sub-Routine http://community.qlik.com/docs/DOC-6940 to create the four calendars.

Create a fifth calendar on top which contains all the dates occuring in the other calendars and dont link it to the data model. Offer this one to the user for selections and use set analysis in your expressions to hand over the selection to the desired calendar.

Hope this helps

egards

Tobias

Re: Handling Multiple Dates in a Single Report

Tue, 17 Feb 2015 08:21:30 GMT

Hi Jagan,

I had already gone through the above link but its not working out. The issue with 4 Fact table is they all are linked to a single table.

For Eg.

4 Fact Tables :

Sales - Sales_No, Sales Date, Sales_Qty

Order - Order_No, Order Date, Order_Qty

Invoice - Invoice_No, Invoice Date, Invoice_Qty

Billing - Billing_No, Billed Date, Bill Qty

1 Dimension Table :

Customer - Sales_No, Order_No, Invoice_No, Billing_No, Customer Names, Address

Kindly suggest.

Regards

Sumeet

Re: Handling Multiple Dates in a Single Report

jagan — Tue, 17 Feb 2015 08:33:19 GMT

Hi,

Can you try like this

FactTable:

LOAD

'Sales' &'_'& Sales_No AS ID,

Sales Date AS Date,

Sales_Qty,

'Sales' AS DataType

FROM SalesTable;

Concatenate(FactTable)

LOAD

'Order' &'_'& Order_No AS ID,

Order Date AS Date,

Order_Qty,

'Order' AS DataType

FROM OrderTable;

Concatenate(FactTable)

LOAD

'Invoice' &'_'& Invoice_No AS ID,

Invoice Date AS Date,

Invoice_Qty,

'Invoice' AS DataType

FROM InvoiceTable;

Concatenate(FactTable)

LOAD

'Billing' &'_'& Bill_No AS ID,

Billing Date AS Date,

Billing_Qty,

'Billing' AS DataType

FROM BillingTable;

Customer:

LOAD

'Sales' &'_'& Sales_No AS ID

Customer Names,

Address

FROM Customer;

Concatenate(Customer)

LOAD

'Order' &'_'& Order_No AS ID

Customer Names,

Address

FROM Customer;

Concatenate(Customer)

LOAD

'Invoice' &'_'& Invoice_No AS ID

Customer Names,

Address

FROM Customer;

Concatenate(Customer)

LOAD

'Billing' &'_'& Billing_No AS ID

Customer Names,

Address

FROM Customer;

Now create one master calendar.

Hope this helps you.

Regards,

Jagan.

Re: Handling Multiple Dates in a Single Report

Tue, 17 Feb 2015 09:50:24 GMT

Hi Jagan,

After applying above logic I am able to create single Linktable with single Date selection for all the Fact.

But the values aren't matching.

Regards

Sumeet

Re: Handling Multiple Dates in a Single Report

jagan — Tue, 17 Feb 2015 10:10:00 GMT

Hi,

It is difficult to identify the issue unless you attach the sample file. Please attach the sample file and your expected output for certain selections.

Regards,

Jagan.

Re: Handling Multiple Dates in a Single Report

Tue, 17 Feb 2015 10:16:59 GMT

Hi Jagan,

I am attaching my application qvw for your reference. kindly help to resolve the issue.

I have 4 date field (Posting Date) coming from 4 Fact tables. Need to consolidate as 1 Date for selection.

Regards

Sumeet

Re: Handling Multiple Dates in a Single Report

Thu, 19 Feb 2015 10:57:10 GMT

Hi Guys,

Finally got the solution for the above issue.

Thanks for your help Jagan & Tobias.

Regards

Sumeet Vaidya

Re: Handling Multiple Dates in a Single Report

tobias_klett — Fri, 20 Feb 2015 10:37:00 GMT

Hi Sumeet,

it would be very interesting to know what your final solution was.

Regards

Tobias

Re: Handling Multiple Dates in a Single Report

Fri, 20 Feb 2015 10:46:55 GMT

Hi Tobias,

I created a Link Table from the 4 Fact. Associated the Link table with all the Facts with a Single Link Key.

Also created another Key in the Link table which would be associated to the Dimension and Concatenated all the Date fields into a single column.

It worked and the Values are matching as well.

Regards

Sumeet Vaidya