Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dates between dimensions and complete Dates

Hi everybody !

 

I have the following problem:


I have 2 dimensions (Date and Branch) and a table of facts (Sales).
The Branch dimension is related to my table of facts through the Branch_ID, in addition this table contains "Open Date" and "Closing Date", not all rows have the full date, there are null fields in "open dates" and in many more in "Closing Date" since this information is only for closed branches.

 

On the other hand, my table of facts is related through a "date" field with my date dimension.

What I need to know is how many branches opened and closed in a given month using the date dimension filter.
I had planned to pass the 2 dates (Open and Closed) to my fact table and then create measurements using USERRELATIONSHIP, but I didn't know what to do with all the rows in null.

 

What do you recommend I do? How would you solve this problem?

 

Thanks!
Regards !!

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Join the date dimension with both the dates. And then use userelation to choose one. When you using the close date. Add filter to calculate formula to have not(isblank(cloase_date)) filter