Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
pauledrich
Contributor

Master Calendar with link table

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


Tags (1)
1 Solution

Accepted Solutions
pauledrich
Contributor

Re: Master Calendar with link table

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

4 Replies
pauledrich
Contributor

Re: Master Calendar with link table

Any ideas experts?

andrefpc
Contributor

Re: Master Calendar with link table

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

pauledrich
Contributor

Re: Master Calendar with link table

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
Contributor

Re: Master Calendar with link table

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.

Community Browser