Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
educastri83
Creator
Creator

Two dates in same Fact Table (+ Master Calendar)

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:

Example.png

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

1 Solution

Accepted Solutions
its_anandrjs

Go with this thread you have to use Canonical dates

Canonical Date

View solution in original post

8 Replies
educastri83
Creator
Creator
Author

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

its_anandrjs

Go with this thread you have to use Canonical dates

Canonical Date

Clever_Anjos
Employee
Employee

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

educastri83
Creator
Creator
Author

it works!

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

Thanks! its.anandrjs

educastri83
Creator
Creator
Author

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
Partner - Contributor II
Partner - Contributor II

Hi EDUARDO

How you did it, i have the same problem

could you share your solution or the script

Thanks

poojabhavanivallem
Contributor
Contributor

Hi EDUARDO,

 

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