Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Master Calendar Script not working

Hello experts,

My Master calendar script is loading fine but the list box selections are not reflecting any changes.

What am I trying to achieve ?

When I select any Quarter or Month-Year in the List Box, then the Total number of Orders are not changing. I want them to change based on what quarter  or month-year is selected.

What I have Tried So far?

My Application has too many date fields so I have created a Date Link table to create a Master Calendar by using Master Calendar script by jgd‌ in his post Creating A Master Calendar

About Data Model

  • I use QVDs which are generated from SQL
  • Each Date Field is a UTC Timestamp
  • I convert them into LocalTime when using those QVD in any application.

I have attached a zip file containing QVD files and a sample QVW.

Can you please advice if anything wrong with my script?

1 Solution

Accepted Solutions
rubenmarin

Hi Lokesh, your dates from orders are DateTime meanwhile the dates from your calendar are only dates (as usual), just use floor to remove the Time part of the order dates, ie:

Date(floor(ConvertToLocalTime(CompletionDate)))

To explain that: each day counts as '1', and 12 hours are '0.5'... your calendar creates integer numbers meanwhile the data readed from Order has decimals so there is no coincidence between the data readed and the calendar, using floor converts the dates from the orders in integer numbers (rounding down to keep it in the same day), so they found his calendar coincidence.

Nice post! sample, qvds, well structured and also colored!

Edit: Use floor in the DateLink table:

Load    OrderId,

                Date(Floor(CompletionDate))            AS     Date,

                'Completion Date'        AS    [Date Type]

Resident Order;

...

View solution in original post

8 Replies
rubenmarin

Hi Lokesh, your dates from orders are DateTime meanwhile the dates from your calendar are only dates (as usual), just use floor to remove the Time part of the order dates, ie:

Date(floor(ConvertToLocalTime(CompletionDate)))

To explain that: each day counts as '1', and 12 hours are '0.5'... your calendar creates integer numbers meanwhile the data readed from Order has decimals so there is no coincidence between the data readed and the calendar, using floor converts the dates from the orders in integer numbers (rounding down to keep it in the same day), so they found his calendar coincidence.

Nice post! sample, qvds, well structured and also colored!

Edit: Use floor in the DateLink table:

Load    OrderId,

                Date(Floor(CompletionDate))            AS     Date,

                'Completion Date'        AS    [Date Type]

Resident Order;

...

Not applicable
Author

Thank you Ruben, it worked

I always try to post my questions very clear so I don't waste much time of other people

Also, I am new to Qlik so I have one more question related to this post :

  • Can this master calendar script be utilised to get more granularity at time level?
agni_gold
Specialist III
Specialist III

Hi Lokesh ,

PFA the app , hope this help , please do not go for date link table directly , create one master script for calendar.

I am not sure date link table is required, and date should link to created date. But may be this is your requirement.

rubenmarin

You can try to iterate based in granularity, if you want to add hours -and a day has 24 hours- you just need to set the steps so here is a coincidence between your DateLink and your calendar, so there is steps of 1/24.

In link:

Date(Floor(CompletionDate, 1/24))   AS     Date

In calendar:

Floor($(varMinDate) + Iterno()/24 -1, 1/24)                                        As Num, 

Floor(Date($(varMinDate) + IterNo()/24 -1), 1/24)           AS TempDate 

AutoGenerate 1 While $(varMinDate) + Floor(IterNo()/24, 1/24) -1 <= $(varMaxDate);

If you want more granularity or there is a lot of historical data, maybe you may take a look at this post: The Master Time Table

Not applicable
Author

Thank again Ruben,

I tried your code but it didn't work.

Would you mind attaching sample QVW?

Thank you in advance

Not applicable
Author

Thank you Kumar,

I found my answer in the previous post. But yea it is my requirement to have Master Calendar linked to Date link table due to multiple date type selection requirement.

Thank you for you input though

rubenmarin

Hi Lokesh, I don't really tested but seems works, I upload 2 versions:

- The first one with one master calendar, with hour granularity.

- The second with a master calendar and a master time table, with minute granularity.

You can check in "configuration -> document properties -> Tables" the number of records for each option... as an advance: the second option has less number of records and less distinct values even having more granularity.

Not applicable
Author

Hi Rube, Thanks for uploading QVW.

I have posted another question on the forum which is bit more complex this time.. here is the link below. Your time and help will be highly appreciated

how to calculate days between two timestamps