4 Replies Latest reply on Oct 19, 2012 2:20 PM by Ken Zheng

    How to join two fact table with a common dimension at different granularity

    Ken Zheng

      Hi

       

      In tableau, how could I join two fact table with a common dimensional table at different granularity to create a view.

       

      I have two fact tables:

      1) daily sales:  sale # at daily level

      2) Monthly target: target # for each month.

       

      I have one dimensional table, dDate, which is linked to the two fact tables at different granularity.

      dDate has one date per row in the table, the fields in dDate:

      DayDate, WeekDate, Month, Year ....

       

      The daily sales is mapped to DayDate field, and monthly target is mapped to Month field.

       

      When I join the 3 tables together in Tableau to create a view, comparing monthly sale and monthly target, The aggregation of target # is counted at daily level instead of monthly level. That completely screwed up the number.

       

      What do I miss here?

       

      How to create a view to compare the aggregated daily sales to month level with monthly target?

       

      Thank you