4 Replies Latest reply: Feb 10, 2014 4:26 AM by Andre Costa RSS

    Master Calendar with link table

    Paul Edrich

      Thanks in advance for any help on this.

       

      I have created a single dashboard from a number of QVD's which include multiple date fields & Tables - I  am experiencing some issues which I believe to be date related, for example my sales total for the last 30 days is different in one table if I use a date & Location as a dimension compared to another if I use say - Sum({$<DaysAgo={'<30'}>Sales) with only the location as the dimension.

       

      My dashboard is accounts based and therefore I want to have a single date reference - in my DateLink table this is 'CommonDate' all my QVD's and Data tables are connected to my Link table via a UniqueID , Dates as CommonDate and  Types as DateType.

       

      The problems

       

       

      The Master calendar (MC) doesn't appear work as it should.

       

      1. If the MC creates the dates from the TempDate as CommonDate from the TempCalendar, date ranges are created fine but I loose most of the data from my tables - this maybe a date format issue from the QVD's as when I preview the tables some have DD/MM/YYYY, Some are DD/MM/YYYY hh:mm:ss, and some are 41233. I use 'Date (Floor(TempDate), 'DD/MM/YYYY')  as Date' in the script for the MC ,

       

      If I create the calendar from my CommonDate (This creates around 5m lines) table, the data is present with the issues I described earlier.

       

      2. In my Table of movements  I want to be able to Avg the daily sales for the last 30 days and had hoped a simple 'Sum({$<DaysAgo={'<30'}>Sales)/30'  would give me the correct result.

       

      As mentioned any help is appreciated, thanks.

       

      P