Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
jblank
Contributor III
Contributor III

Comparing two different dates but on MonthYear level in Set Analysis

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

Labels (1)
1 Solution

Accepted Solutions
jblank
Contributor III
Contributor III
Author

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

View solution in original post

9 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor III
Contributor III
Author

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:

jblank_0-1689342022901.png

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

Best Regards

Joerg

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor III
Contributor III
Author

Hello Oleg,

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

jblank_2-1689344005579.png

 

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 

jblank_1-1689343719715.png

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
Contributor III
Contributor III
Author

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 😉

jblank_0-1689346338320.png

Best Regards

Joerg

jblank
Contributor III
Contributor III
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor III
Contributor III
Author

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