0

Multiple Dates on child table joined to Audit.Date

I have a table with multiple dates that have been joined to the Audit.Date table to take advantage of the meta data from the date dimension (month name for example.) The problem is when I use an insight to group by the month name, I cannot find a way to control which date field from the child table to use.

The table has CreateDate, UpdatedDate, and PaymentDate all joined to Audit.Date.date. Using a column chart, I use Month Name as the grouping dimension. What I observe is that the CreateDate is being used as the underlying pull for rows. I need to use the UpdateDate.

I am also wanting to summarize by the month and not the individual dates.

How can I accomplish this?

11replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Marc, you have created a relationship between which column of your child table with the audit.table.

    also the sequence of joins. As per my observation, I think since the join is first on CreateDate and then on other dates, Incorta is using the first join by default for populating the data.

    Regarding summarizing by month, can you please elaborate.

    Reply Like
    • soumya b Thanks. I understand how to use the meta dates. I was not aware of the default order though. I have altered the order of my joins and it worked as I expected. I would still like to have a way to control the selected date apart from the default.

      Reply Like
  • control the selected date means.. do u want to select dates during runtime

    Reply Like
    • soumya b Control the date means control which date is used for the join to the date dimension. In my example, I would want to have an additional control on the measure field or in the insight filter area that would specify the date dimension to base the join to the date table.

      Reply Like
    • Marc Paige control the date I understand you mean to say you should have an option to directly select if the join has to be based on CreateDate/UpdatedDate/PaymentDate to join to the date dimension. AFAIK there is no option to dynamically select the respective date column to join. But we can consider an alternate approach:

      create an alias table for the date dimension, say date_1 and join it to the respective column in the child table.

      When computation is needed based on the respective date, select that particular alias related columns.

      hope this helps..

      Reply Like 1
    • soumya b Thanks. I will give that a try.

      Reply Like
    •  soumya , I tried the alias trick and it worked. It is cumbersome though. 

      Reply Like
  • cool.. happy to now it worked!!

    Reply Like
Like Follow
  • 7 mths agoLast active
  • 11Replies
  • 74Views
  • 2 Following