topic Using Master Dates for other Date fields in QlikView

Using Master Dates for other Date fields

triciagdaly — Fri, 08 May 2015 15:50:01 GMT

I have a master date, along with master year, month, and quarter that I'm attempting to use for another set of date fields, Book_Year, Book_Month, and Book_Quarter in selecting data. In the application attached, see the Turn Time chart, the data is showing all possible values, instead of for Booked_Month. when only selecting from Master Year and Master Month. Master Dates can be very confusing, so I've tried to simplify what I'm attempting to do.

The purpose of the below expression is to calculate the total of the difference of MSPD Date added and Booked Date, either by Sales Order Lines or Serial Numbers. It 'seems' to be working for SO Lines, but not Serial Number. I've only noticed the issue when grouping by Serial Number.

I am using this expression:

if(Toggle = 'SO Lines',
sum(
aggr(
sum( {< [MSPD Date Added] = {"*"}, [Booked Date] = p(Master_Date) >} DISTINCT num(([MSPD Date Added])) - num(([Booked Date])))
,_orderlinecompkey)),

sum(
aggr(
sum( {< [MSPD Date Added] = {"*"}, [Booked Date] = p(Master_Date) >} DISTINCT num([MSPD Date Added]) - num([Booked Date]))
,[Serial Number]))

)

The Master Date filters aren't working as I'd hoped and the data only seems correct, when I use the specific Booked Date filters. I can hide the Master Date filters for this chart if necessary and only use the Booked Date filters but had hoped to use a Master Date and it work for all charts.

I've also attached screen shots as additional reference.

Re: Using Master Dates for other Date fields

Fri, 08 May 2015 17:55:57 GMT

Hi I was unable to open your document..does it have security enabled on it?

Re: Using Master Dates for other Date fields

triciagdaly — Fri, 08 May 2015 18:41:26 GMT

How do I remove the hidden script or disable it?

Re: Using Master Dates for other Date fields

triciagdaly — Fri, 08 May 2015 19:50:51 GMT

Security has been disabled.

Re: Using Master Dates for other Date fields

petter — Mon, 11 May 2015 19:43:07 GMT

Date handling can be quite a challenge sometimes.

A good read is Henric Cronström's excellent article about Canonical Date‌ It is worth reading more than once and keeping for reference.

Remember that you can always use both slider and calendar sheet object and link them to variables without needing to reference date fields for that...