Year to Date set analysis

[jason_nicholas]

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)?

[sunny_talwar]

Here are all the parenthesis:

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

[jason_nicholas]

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

[sunny_talwar]

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])

[jason_nicholas]

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.

[sunny_talwar · Accepted solution]

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])

[jason_nicholas]

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

[jason_nicholas]

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

[sunny_talwar]

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

[sunny_talwar]

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

[jason_nicholas]

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.