Master vs AutoCalendar & two date dimensions

[Anonymous]

Hi all,

First question posted here. I've been browsing the various sources for a solution and have found various possible solutions, but i'm not sure what would be the best solution/combination for my case.

The case in itself is simple, i've got a table (Order), with two datefields: OrderCreateDate and OrderFinishDate.

I want to create on chart with both combined, so for each (ie) month, a calculation of all created and all finished orders. Or even calculated (Current Portfolio size is all orders which have a createdate but not yet a finishdate at a set moment in time).

I've found the master calendar options as well as the canonical date‌ and the blog regarding Master Table with multiple roles.

All combined making for some light reading...   Unfortunately, for me, not light enough...

So with the goal mentioned above,

- what approach should I take, taken into account that i'm using Qlik Sense.

- do I have to do something with AutoCalendar, does it conflict with a master calendar?

Many thanks for your reply.

[acbishop2]

Hello,

I've had to figure out a similar function to combine several date fields (up to twelve at a time) into the same timeline, and this is what I have come up with. If I understand your question well, this will work.

You want to combine the two timelines into one, so you'll need to create a cross table using the CROSSTABLE function in the script. Let's say your data is separated into the following fields:

OrderID

OrderCreateDate

OrderFinishDate

I'm sure there are more fields, but for the sake of simplicity, we'll continue with this. To combine the two date fields, use this function in the load script:

[Table Name]

CROSSTABLE(Event,Date,1)

LOAD

     OrderID

     OrderCreateDate AS [Order Created]

     OrderFinishDate AS [Order Finished]

From [source reference]

That is the simple version. It will likely have to be edited to fit the data that you have, but you should be able to understand it well if you check out these videos:

The Power of Qlik Script - Reshaping Data using Crosstable - YouTube

The Power of Qlik Script - Reshaping Data - Part 2 - YouTube

The Power of Qlik Script - Reshaping Data - Part 3 - YouTube

I hope that helps! If it wasn't clear enough, let me know!

[bramkn · Accepted solution]

Hi Jacob-Jan,

I would advise you to never use AutoCalendars. They can be a pain in the butt when doing anything that is just a little bit complicated. For your usecase you should probably use the "canonical date" option.

The AutoCalendar can be ignored or removed.

Hope this answers your question.

[Anonymous]

Thank you guys for your reply.

I went on the 'drop autocalendar and start with master calendar' road.

However, i've got an issue in my loadscript, not sure if it's because of the field type in my database or my loadscript, but i'm getting an error in my master calendar script.

Will post that as a new question and get back to this as soon as i've been able to solve it (with the communities help )

[Anonymous]

After some additional trial & error, probably based on my non-expert knowledge, the canonical date seems to do the trick.

I still have some weird behaviour in data visualisation, but will have to dig deeper to be able to find the issue there.

Thanks for your input, i'll mark this question as resolved.

[bramkn]

Goodluck! If I got time And see your question I will try to help of course.