topic Re: Two dates in same Fact Table (+ Master Calendar) in QlikView

Two dates in same Fact Table (+ Master Calendar)

educastri83 — Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone!!

I need to solve this:

In my model, I have several date fields in a single table called "Fact_Table". The data fields are:

==> OrderDate

==> PaymentDate

The Data model contains a Master Calendar table linked only by "OrderDate" field, but I want to manage both date fields in the same master calendar, with a generic date field. I do not know how to do...

An example (or idea) in the image below:

I would like it to be available in the dashboard advanced selections like “orders placed in september but payment in october, and visualize KPI's like "orders placed in current month Vs. last month", or "Quantity Invoice payment in current month", among others. (using set analysis and points in time reporting)

I must comment that "Fact_Table" contains several conditional fields and I have used "Apply map" function in this fact table. (see the example.qvw file)

I really appreciate your help, I think it's not difficult but I'm still learning advanced script.

Eduardo

Re: Two dates in same Fact Table (+ Master Calendar)

educastri83 — Thu, 05 Oct 2017 03:11:36 GMT

Hi! stalwar1‌ you always help me! please check out!

Re: Two dates in same Fact Table (+ Master Calendar)

its_anandrjs — Thu, 05 Oct 2017 04:19:21 GMT

Go with this thread you have to use Canonical dates

Canonical Date

Re: Two dates in same Fact Table (+ Master Calendar)

Clever_Anjos — Thu, 05 Oct 2017 10:53:52 GMT

The "classical" solution for this case is the Canonical Date table, as its.anandrjs‌ pointed

Re: Two dates in same Fact Table (+ Master Calendar)

educastri83 — Thu, 05 Oct 2017 18:31:16 GMT

it works!

previously it had not used a cannonical date, it is very functional. Looks like:

Thanks! its.anandrjs‌

Re: Two dates in same Fact Table (+ Master Calendar)

educastri83 — Thu, 05 Oct 2017 18:34:34 GMT

Yes! it its and works fine with set analysis using my new datatype field.

Thanks

Re: Two dates in same Fact Table (+ Master Calendar)

its_anandrjs — Thu, 05 Oct 2017 18:36:04 GMT

Great Work Now can you close the thread with appropriate answers as correct and helpful for references.

Regards

Anand

Re: Two dates in same Fact Table (+ Master Calendar)

medlarayedh — Tue, 12 Jun 2018 10:27:31 GMT

Hi EDUARDO

How you did it, i have the same problem

could you share your solution or the script

Thanks

Re: Two dates in same Fact Table (+ Master Calendar)

poojabhavanivallem — Thu, 14 Oct 2021 16:41:37 GMT

Hi EDUARDO,

Could you share the script because i am also facing same issue