Very basic set analysis question

[#1]

Hi Everyone,

I am new to Qlik Sense and have a very straightforward question.

I have watched a number of videos on set analysis and do not understand how you can reference "Year" in the set analysis without stating what date field in the data you are trying to use.

For example, something as simple as Sum({<Year={"2015"}>}Sales)

I, for example, what to look at sum of sales when Booked Date = 2015.

I understand the syntax, but for some reason can't get this to work. All of the examples online seem to have these vague date fields being used which I do not entirely understand.

I know this is very dense of me and I'm likely overthinking this, but any help would be greatly appreciated.

Thanks!

[agni_gold]

from where you are getting year field , have you created year from booked date ?

[swuehl]

"I have watched a number of videos on set analysis and do not understand how you can reference "Year" in the set analysis without stating what date field in the data you are trying to use."

If I understood correctly, you can't. Set analysis is all about selections you apply to fields in your data model, so you need to state the field(s).

Can you point to a video or sample which demonstrates a reference of "Year" in set analysis without stating any field?

(Maybe I just misunderstood the issue. There are different approaches how to filter a "Year" e.g. in a calendar Date field, i.e. you can filter the year in a "Year" field or search all dates of the year in a "Date" field, but you need to state a field from your model)

[Lech_Miszkiewicz]

When you execute script ususally you create extra fields from Date like, Month, Year, YearMonth, Day etc..

Above expression refers actually not to date field but to Year field (column) in data model.

If you have 2 dates in one row it becomes then more difficult.

You can either create other columns for your Booked Date, like BookedYear, BookedMonth etc.. and then use it like:

Sum({<BookedYear={"2015"}>}Sales)

However it then relies not on one but many calendars.

So the other solution would be to create linking table linked through all different date types with date type description and creating master calendar linked to it.

I might be able to give you a simple example later.

regards

Lech

[#5]

I think I figured out my first issue

I am able to bring the date field into an expression as [Booked Date.autoCalendar.Year] which I tested and works for a basic calc like Sum({<[Booked Date.autoCalendar.Year]={"2015"}>}Sales)

I have been googling everywhere to figure out how to calculate YTD/QTD/MTD based on the dates in my data (vs. creating a calendar such as what's being done here: The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync)

Do you guys have any idea how this can be done? Have been trying for a bit now and can't seem to figure out how to create these date ranges

Thanks everyone for the quick responses on this

[Lech_Miszkiewicz · Accepted solution]

Hi Mike,

Well - You have just indicated that you are using Qlik AutoCalendar functionality which sort of deals with dates and formating and i am sure it is useful for Continous scaling etc.

However you have no control of how to use fields created in this calendar or at least it would be a way more hassle to explain that to you.

In my opinion you should read little bit about scripting, going to script editor and build your own calendar script instead the one which got generated automaticly.

Only then you will hava more control on how date fields are linked and how data is transformed. So once you get to script editor place your coursor somewhere at the end of script and type:

Calendar:

Load Distinct

     Booked Date,

     Date(Booked Date) as Date,

     Month(Booked Date) as Month,

     Day(Booked Date) as Day

     Year(Booked Date) as Year

Resident

     (here place table name where Booked Date is kept)

;

Then do little more reading about calendars and scripts and YTD,MTD,QTD, MAT and other date groupings.

It is not an easy topic for newbe.

regards

[#7]

Ok sounds good, thanks for breaking that down and apologies for the newbie questions

Will look further into this and give it another shot

Thanks again

[Lech_Miszkiewicz]

No need to apologise - thats what we are all here for - always happy to help. You may find a lot of topics on AskQv.com - go to search section and search for calendar/ master calendar etc.

Or Just start with this:

The Master Calendar

regards

Lech

[#9]

Great, thanks again!