topic Re: Master Calendar Script not working in QlikView

Master Calendar Script not working

Sun, 27 Mar 2016 09:18:29 GMT

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?

Re: Master Calendar Script not working

rubenmarin — Sun, 27 Mar 2016 10:10:25 GMT

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;

...

Re: Master Calendar Script not working

Sun, 27 Mar 2016 10:22:22 GMT

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?

Re: Master Calendar Script not working

agni_gold — Sun, 27 Mar 2016 10:28:46 GMT

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.

Re: Master Calendar Script not working

rubenmarin — Sun, 27 Mar 2016 15:03:53 GMT

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

Re: Master Calendar Script not working

Mon, 28 Mar 2016 09:15:10 GMT

Thank again Ruben,

I tried your code but it didn't work.

Would you mind attaching sample QVW?

Thank you in advance

Re: Master Calendar Script not working

Mon, 28 Mar 2016 09:16:31 GMT

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

Re: Master Calendar Script not working

rubenmarin — Mon, 28 Mar 2016 19:07:46 GMT

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.

Re: Master Calendar Script not working

Sun, 03 Apr 2016 00:27:02 GMT

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