2 Replies Latest reply: Aug 17, 2015 10:22 AM by Andy Weir RSS

    Date dimension

      Hi, I have a problem with my data model.

      Actually in our software We don't have date table and in every table, related dates are considered but when I create a table in qlikview as date and want to reference the date fields to this table, I confront with loop problem. In my data model, Order is fact table and other tables are connected to this table like customers, products, category products, vendors and so on. I set orderCreatedOnDate as Date in OrderDimension AND set PromotionStatrDate as Date in PromotionDimension and ProductCreatedOnDate as Date in ProductDimension. Then I faced with loop.

      So what can I do to solve my problem and avoiding loop ?

        • Re: Date dimension
          Stefan Wühl

          First ask yourself if you really want to create a common date field for these semantically different dates:

          Canonical Date

          • Re: Date dimension
            Andy Weir

            I would have separate dates an orderdate, promotiondate, productdate in the dimension tables.  I would then have a master calendar that I would wire into the appropriate date in my measures for my charts.  so an Order chart I would include as

             

            SUM({$ <[OrderDate]=p([Date])>} your value) for example.

             

            Another approach could be load all your dates as facts and have an OrderKey, CustomerKey, PromotionKey and Productkey for your various transactions each with a date depending on the type of Fact.  That way you can keep one date field for all your dimensions if that is appropriate.

             

            Hope this helps

             

            Regards

             

             

            Andy