Handling Multiple Dates in a Single Report

[#1]

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

[jagan]

Hi Sumeet,

Check this link for solution

Canonical Date

Regards,

Jagan.m

[tobias_klett]

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

[#4]

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

[jagan]

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.

[#6]

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

[jagan]

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.

[#8]

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

[#9 · Accepted solution]

Hi Guys,

Finally got the solution for the above issue.

Thanks for your help Jagan & Tobias.

Regards

Sumeet Vaidya

[tobias_klett]

Hi Sumeet,

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

Regards

Tobias