Master Calendar with link table

[pauledrich]

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

[pauledrich]

Any ideas experts?

[andrefpc]

I would suggest to format every date field to the same type. Do you get wrong results or no dates at all?

[pauledrich · Accepted solution]

Thanks Andre

If I use the 'CommonDate' as the MC data - all data is present, if I use TempCalendar then only data from one QVD is showing.

Would I format using 'Date (Floor(QVDdate), 'DD/MM/YYYY')  as LinkDate' ?

P

[andrefpc]

If I'm not getting this wrong...the Tempcalendar fied is the data field from one QVD, is it present, with the same name on the other QVDs you're using?

I think date(QVDate, 'DD/MM/YYYY')  as LinkDate is enough.