Two dates in same Fact Table (+ Master Calendar)

[educastri83]

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

[educastri83]

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

[its_anandrjs · Accepted solution]

Go with this thread you have to use Canonical dates

Canonical Date

[Clever_Anjos]

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

[educastri83]

it works!

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

Thanks! its.anandrjs‌

[educastri83]

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

Thanks

[its_anandrjs]

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

Regards

Anand

[medlarayedh]

Hi EDUARDO

How you did it, i have the same problem

could you share your solution or the script

Thanks

[poojabhavanivallem]

Hi EDUARDO,

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