topic Re: Year to Date set analysis in QlikView

Year to Date set analysis

jason_nicholas — Wed, 25 Nov 2020 16:16:04 GMT

I have searched a number of threads to determine the set analysis function for displaying year to date data. I don't fully understand how this function works, and I am not getting the expected results. I would appreciate feedback and corrections to my approach.

Here is the function I am using

Count(DISTINCT{$<[Event Flag]={'Event Flag'},Status={'Active'},[Test Filter]={'Remove Testing'},Year={$(=Max(Year))},Month={"<=$(=max(Month))"}>}[Booking ID])

I have region as a dimension, and the goal is to show a pie chart distribution of the number of [Booking ID]s that fit a specific description per region, to date (or potentially through the last completed month). When I add in the highlighted year to date function above, the chart totals are still measuring the entire year instead of year to date.

My date field is loaded by year, month, and day separately. In addition, all of the data in this report only includes the current year, so maybe I can remove the Year setting altogether?

Can someone explain in plain text how this function is supposed to work? How does it look at the current date (or date of last update)? It is my understanding that max(month) is looking for the maximum month listed in the data, but my data source looks ahead as well as behind, so the max(month) is always going to be December.

Is there a different function I could use to measure the number of bookings in my report that occur before a set date (today, the last reload date, or the end of the previous month)?

Re: Year to Date set analysis

sunny_talwar — Wed, 15 Feb 2017 16:49:56 GMT

I would recommend to create a Month field with numeric representation

LOAD Month,

Num(Month) as NumMonth

...

FROM ...

next if you need the YTD as of the current month, Max(Month) isn't going to work. Instead use Today's month

Count(DISTINCT{$<[Event Flag] = {'Event Flag'}, Status = {'Active'}, [Test Filter] = {'Remove Testing'}, Year = {$(=Max(Year))}, Month = {"<=$(=Num(Month(Today())))"}>}[Booking ID])

Re: Year to Date set analysis

jason_nicholas — Wed, 15 Feb 2017 18:44:26 GMT

This works well. I had to use a preceding load for NumMonth, because my primary load uses

LOAD

Month([Booking Date] as Month

But, once I got that, the function worked. I am not fully clear on the syntax, particularly in the use of the parentheses.

I see that it says "select the year that is the maximum year in the data", and select the month (by number) that is less than or equal to today's month number, but what does the additional () mean? I have seen it in a number of threads, and I can't quite get what it is for.

Re: Year to Date set analysis

sunny_talwar — Wed, 15 Feb 2017 18:50:26 GMT

You don't have to do preceding load, you can do this

LOAD [Booking Date],

Month([Booking Date]) as Month,

Num(Month([Booking Date])) as NumMonth

I see that it says "select the year that is the maximum year in the data", and select the month (by number) that is less than or equal to today's month number, but what does the additional () mean? I have seen it in a number of threads, and I can't quite get what it is for.

Additional ()? Where?

Re: Year to Date set analysis

jason_nicholas — Wed, 15 Feb 2017 18:59:28 GMT

A further question:

Am I correct in assuming the function will give me the total through the end of the current month? Or would it be accurate to the day?

My data has 5 bookings in January, and 1 in February. The February booking has already happened, so I get an accurate result of '6'. Would this correctly calculate as '5' if the February booking were in the future?

Re: Year to Date set analysis

sunny_talwar — Wed, 15 Feb 2017 19:05:58 GMT

This will give you the correct result to the end of the month.

=Num(Month(Today()))

will be 2 regardless if it is 1st Feb or 28th Feb. So, all data for Feb will be included. Do you want just until today?

Re: Year to Date set analysis

sunny_talwar — Wed, 15 Feb 2017 19:08:22 GMT

For accuracy of today's date, I would use this

Count(DISTINCT{$<[Event Flag] = {'Event Flag'}, Status = {'Active'}, [Test Filter] = {'Remove Testing'}, Year = {$(=Max(Year))}, Month, [Booking Date] = {"<=$(=Date(Today(), 'DateFieldFormatHere'))"}>}[Booking ID])

Re: Year to Date set analysis

jason_nicholas — Wed, 15 Feb 2017 19:11:00 GMT

I think I misunderstood how the statement works. I though the Month (in bold, below) was a reference to the field I created above it. Now I understand it is a function in itself. Thanks for the clarity

LOAD [Booking Date],

Month([Booking Date]) as Month,

Num(Month([Booking Date])) as NumMonth

the statement Today() which is then followed by the three parentheses closing out the function. I don't get what the () at the end of Today (or other functions I have seen it in) means.

Re: Year to Date set analysis

jason_nicholas — Wed, 15 Feb 2017 19:16:22 GMT

my date field is simply called DAY. Just to confirm I use correct syntax, would it be:

Count(DISTINCT{$<Location={'External'},Status={'Active'},[Test Filter]={'Remove Testing'},Year = {$(=Max(Year))}, Month, [Booking Date] = {"<=$(=Date(Today(), Day))"}>}[Booking ID])

this gave me the correct result, but I just want to be sure I fully understand what I am putting. This additional syntax will take some digging to comprehend.

I don't know where I'd be without this message board. Thank you all

Re: Year to Date set analysis

sunny_talwar — Wed, 15 Feb 2017 19:19:54 GMT

Booking Date is just a Day? Not sure I understand... Can you share a screenshot of how Booking Date looks? Also, look here:

Dates in Set Analysis

Re: Year to Date set analysis

sunny_talwar — Wed, 15 Feb 2017 19:21:35 GMT

Here are all the parenthesis:

<=$(=Num(Month(Today())))

Re: Year to Date set analysis

jason_nicholas — Wed, 15 Feb 2017 19:23:42 GMT

LOAD

[Booking Date],
Year([Booking Date]) as Year,
Month([Booking Date]) as Month,
NUM(Month([Booking Date])) as NumMonth,
Day([Booking Date]) as Day,

I can't upload screen shots, but this is how I load the date

Re: Year to Date set analysis

sunny_talwar — Wed, 15 Feb 2017 19:26:16 GMT

Do like this:

LOAD Date([Booking Date]) as [Booking Date],
    Year([Booking Date]) as Year,
    Month([Booking Date]) as Month,
    NUM(Month([Booking Date])) as NumMonth,
    Day([Booking Date]) as Day,

No need for Date format anymore...

Count(DISTINCT{$<[Event Flag] = {'Event Flag'}, Status = {'Active'}, [Test Filter] = {'Remove Testing'}, Year = {$(=Max(Year))}, Month, [Booking Date] = {"<=$(=Date(Today()))"}>}[Booking ID])

Re: Year to Date set analysis

jason_nicholas — Wed, 15 Feb 2017 19:30:30 GMT

this has all been amazing, and informative. I will use the link you provided to help clarify my understanding of the function, but for now, at least I can move on to finding my next problem. Thank you for your help.

Re: Year to Date set analysis

sunny_talwar — Wed, 15 Feb 2017 19:35:21 GMT

No problem at all. While we are here, I am going to provide you one other way to approach this problem assuming your application reloads everyday

LOAD Date([Booking Date]) as [Booking Date],

Year([Booking Date]) as Year,

Month([Booking Date]) as Month,

NUM(Month([Booking Date])) as NumMonth,

Day([Booking Date]) as Day,

If([Booking Date] <= Today() and Year = Year(Today()), 1, 0) as Flag

and then you need just this:

Count(DISTINCT{$<[Event Flag] = {'Event Flag'}, Status = {'Active'}, [Test Filter] = {'Remove Testing'}, Flag = {1}>}[Booking ID])

Re: Year to Date set analysis

jason_nicholas — Wed, 15 Feb 2017 20:05:33 GMT

That is much cleaner, and will be easier to describe in my documentation. Kudos

Re: Year to Date set analysis

jason_nicholas — Wed, 15 Feb 2017 20:16:19 GMT

I used your exact syntax, but it gave me an error, saying it couldn't find field YEAR. I expect this is because YEAR doesn't exist until I load it in this statement.

I tried it without the YEAR function, because my source data will always only contain the current year, and never any other

IF([Booking Date]<= Today(),1,0) as Flag,

This worked, but I have an interesting NULL result on tomorrow's date. Not every one, just one single entry. this won't impact the function, but I don't understand why that would be the case

Re: Year to Date set analysis

sunny_talwar — Wed, 15 Feb 2017 20:17:48 GMT

My bad, try this:

LOAD Date([Booking Date]) as [Booking Date],

Year([Booking Date]) as Year,

Month([Booking Date]) as Month,

NUM(Month([Booking Date])) as NumMonth,

Day([Booking Date]) as Day,

If([Booking Date] <= Today() and Year([Booking Date]) = Year(Today()), 1, 0) as Flag

Re: Year to Date set analysis

sunny_talwar — Wed, 15 Feb 2017 20:18:34 GMT

NULL result on tomorrow's date? What does that mean? Can you elaborate?

Re: Year to Date set analysis

jason_nicholas — Wed, 15 Feb 2017 20:25:45 GMT

I created a table of Booking ID, Booking Date, and Flag, to test the results of the Flag function. It worked on every single entry except the one. But, as it turns out, it was a separate and unrelated error. I sorted it out.