Comparing two different dates but on MonthYear level in Set Analysis

[jblank]

Hello, 

I wonder if someone can advice on following problem:

I have two different date fields in  my App. The major one is associated with MasterCalendar. In order to prevent circular references I just join on the MasterCalendar date fields.

Hence, one table has a different date field, not being associated with MasterCalendar. There is no need to match it further then on Month and Year - so I tried using this SET Analysis:

COUNT({$<[Activities.Activity_MonthYear]={"$(=Date(MonthStart([MasterCalendar.dtstamp],0),'MMM-YYYY'))"},[Company_Master.Company_Type] = {'Customer'},[Activities.activity]={'Attended Webinar'}>} [Activities.activity_detail])

Field [Activities.Activity_MonthYear] is using same format 'MMM-YYYY'.

The filter I use globally is MasterCalendar.MonthYear (also using 'MMM-YYYY' format). When I choose i.e. MasterCalendar.MonthYear = Jun-2023, I don´t get the same result with the SET Analysis as I would expect when I manually filter for [Activities.Activity_MonthYear] and do the count in source table for very same conditions as above.

II also tried an alternative SET Analysis:

COUNT({$<[Activities.Activity_MonthYear]={">=$(=Date(AddMonths(Min([MasterCalendar.dtstamp]), 0), 'MMM-YYYY'))<=$(=Date(AddMonths(Max([MasterCalendar.dtstamp]),0), 'MMM-YYYY'))"}, Month, [MasterCalendar.dtstamp],[Company_Master.Company_Type] = {'Customer'},[Activities.activity]={'Attended Webinar'}>} [Activities.activity_detail])

But again not working as supposed to.

Does anyone has an idea what might be wrong?

Many thanks,

Joerg

[Oleg_Troyansky]

Hi Joerg,

I think the problem is as simple as a pair of single quotes. Date values in Set Analysis should be enclosed in single quotes, otherwise the engine wouldn't recognize them as dates. So, your formula should look like this:

COUNT({$<[Activities.Activity_MonthYear]={'$(=Date(MonthStart([MasterCalendar.dtstamp],0),'MMM-YYYY'))'},[Company_Master.Company_Type] = {'Customer'},[Activities.activity]={'Attended Webinar'}>} [Activities.activity_detail])

In this case you don't need double quotes - those are used for search conditions. Here, there is no search, just a single date value.

Your second formula does contain search, so the double quotes are needed here, but also the single quotes around the date values:

COUNT({$<[Activities.Activity_MonthYear]={">='$(=Date(AddMonths(Min([MasterCalendar.dtstamp]), 0), 'MMM-YYYY'))'<='$(=Date(AddMonths(Max([MasterCalendar.dtstamp]),0), 'MMM-YYYY'))'"}, Month, [MasterCalendar.dtstamp],[Company_Master.Company_Type] = {'Customer'},[Activities.activity]={'Attended Webinar'}>} [Activities.activity_detail])

Also, I've seen AddMonths(..., 0) a few times lately, and the explanation I got is "to get the date formatted properly". If you also use the Date() function, the AddMonths is unnecessary, in my opinion.

To learn more advanced development techniques,  including advanced Set Analysis, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

[Oleg_Troyansky]

As an afterthought...

You might get a syntax error because of two pairs of single quotes used in the same formula. If you do, then you may have to use Advanced Search in set analysis. For your first formula, it would look like this:

COUNT({$<[Activities.Activity_MonthYear]={"=[Activities.Activity_MonthYear] = MonthStart([MasterCalendar.dtstamp])"},[Company_Master.Company_Type] = {'Customer'},[Activities.activity]={'Attended Webinar'}>} [Activities.activity_detail])

In Advanced search, you don't need to worry about date formatting.

Another afterthought - your chart should not be date-sensitive. In other words, Month, or Date, or any other calendar fields should not be used as chart dimensions - otherwise, this condition wouldn't produce the desired result. Set Analysis cannot be sensitive to chart dimensions.

Also, the way this condition is formulated, it presumes that a single date (or at least a single month?) is selected in the Calendar. What happens if several dates/months are available?

Perhaps you could use the P() function, in combination with the calendar field YearMonth, like this:

COUNT({$<[Activities.Activity_MonthYear]= P(Calendar.MonthYear) ,[Company_Master.Company_Type] = {'Customer'},[Activities.activity]={'Attended Webinar'}>} [Activities.activity_detail])

Cheers,

[jblank]

Hello Oleg,

Thank you so much for your prompt response! Very appreciated.

However, when I  copied and pasted your modified SET formulas, I still don´t get desired results. I tried both and each of it returns 0:

This is regardless if I have chosen any particular MonthYear or none from MasterCalendar.MonthYear Filter.

Best Regards

Joerg

[Oleg_Troyansky]

Hi Joerg,

It's hard for me to troubleshoot without seeing the app, the data model, etc... If you are able to share a small sample, I wouldn't mind to take a look.

Cheers.

[jblank]

Hello Oleg,

Thank you so much for all your help - I think it might be easiest if you see this table:

The most left column is where I limit with a usual filter widget. This is driving most of the widgets in my App directly. But for that single widget 

I want to see only the count of Activities.Activity_detail for the matching MonthYear values and the other constraints (Attended Webinar, Customer) as highlighted.

Does that make sense?

Many thanks once more for your help!!

Best Regards

Joerg

[jblank]

Hello Oleg, 

sorry- one glitch in my screenshot - I highlighted Jun-2023 to be matched June -2022 - sorry: here is the proof that there would be even matching values for  June 2023 😉

Best Regards

Joerg

[jblank · Accepted solution]

Hello @Oleg_Troyansky ,

FYI - based on your responses, I slightly modified my SET formula as follows:

=IF (Len(GetCurrentSelections([MasterCalendar.MonthYear])) >0,
COUNT({$<[Activities.Activity_MonthYear] = [MasterCalendar.MonthYear], [MasterCalendar.MonthYear] = ,[Company_Master.Company_Type] = {'Customer'},[Activities.activity]={'Attended Webinar'}>} [Activities.activity_detail]),
COUNT({$<[Company_Master.Company_Type] = {'Customer'},[Activities.activity]={'Attended Webinar'}>} [Activities.activity_detail]))

The bold part I had to add - simply equal definition for  [Activities.Activity_MonthYear] = [MasterCalendar.MonthYear] while disregarding the  [MasterCalendar.MonthYear] =, since there were a few entries not matching (either blank or different) in [MasterCalendar.MonthYear]. 

The IF/Then statement takes care for default setting of MasterCalendar.MonthYear (no selection).

Thank you since you drove me in right direction!

Best Regards

Joerg

[Oleg_Troyansky]

Hi Joerg,

Glad I could help. If this is working as expected, then - great!

A couple of points to keep in mind. The "simply equal definition" in Set Analysis:

Field1 = Field2

means Select values in Field1 that are equal to selected values in Field2. Which means that the only field that you can use for selections is the MonthYear field. If you wanted to select a Year or a Quarter, or any other calendar fields, this formula wouldn't serve you.

In my experience, a better filter would be to select possible values, like this:

Field1 = P(Field2)

I believe in this case, you wouldn't necessarily need to use the IF condition, because if no selections were made, all values are possible, which is what you need, I believe.

Cheers,

[jblank]

Hello Oleg,

Thanks a lot! I´ll keep that in mind if we might need to add filters for Years or Quarters - as of now, we only select on Month-Year basis - so it should be fine for now.

Best Regards

Joerg