Set Calculation - missing something?

[#1]

Hello,

I have a straight table with 4 columns:

1) MonthYear (a month-year combi like 01-09, 02-09, ..., 09-09)
MonthYear is a field/ the dimension.

2) expression: start of Month
=daystart(monthstart(MonthYear))

3) expression: end of Month
=dayend(monthend(MonthYear))

4) expression: Set Calculation counting distinct time events in an event table in the time range defined by MonthYear.
=count({<FirstRegDateTime={">=$(=daystart(monthstart(MonthYear))) <=$(=dayend(monthend(MonthYear)))"}>} distinct FirstRegDateTime)

I have problems with the 4th column.
It calculates fine if I have just 1 MonthYear selected.
It returns 0 if I have more than 1 MonthYear selected.
When having selected > 1 MonthYear I'd like to achieve 1 row per MonthYear, providing the calculation for the MonthYear of the row.

Columns 2+3 show the correct values for the selected MonthYear(s).

So I assume that I oversee a tiny detail which makes the formula in column 4 work.

Any idea?
Thank you,
Thilo

[#2]

Hi,

this would be better if you attach an sample data file.

I think that the right expression is this (based in my own imaginary example data):

=count({<FirstRegDateTime={'>=$(=Date(daystart(monthstart(MonthYear)))) <=$(=Date(dayend(monthend(MonthYear))))'}>} distinct FirstRegDateTime)

If it won't work, please attach the qvw file.

Best regards.

[#3]

Hello,

thanks for the feedback. Unfortunately the Date function does help. So I reduced the problem to the involved tables/ fields and created the attached qvw which shows the same effect. I hope someone sees what's wrong.

Thank you,

Thilo

[#4]

>>
Unfortunately the Date function does help.
<<

Of course that was meant to be "does NOT help". 😉

[#5]

Hello all,

this topic still bugs me ...

I tried-and-error'ed a bit further coming to an 'almost there' solution.
I reached my goal using an IF formula instead of Set Analysis.
But the IF formula is much(!) too slow in my real data with 100-thousands of events.

Still I don't see the difference between the SET and the IF formula.
Why does the one calculate, but not the other.

Perhaps you can help me...
(See attachment)

Thilo

[johnw]

Dollar sign expansions and set analysis are evaluated ONCE for the entire chart, not once per combination of dimensions in the chart. IF, on the other hand, is evaluated for each combination of dimensions in the chart. So your two expressions, while similar on the surface, do not mean the same thing. When multiple MonthYears are available, you're getting NULL for MonthYear in your set analysis expression, and so it returns 0.

Normally, I would just connect the tables with an intervalmatch (see attached). That solves the problem and will be very fast to process. But you specifically say, "It is by intention that the two tables have no relationship." So why DON'T you want a relationship between a calendar and the events that occur during that calendar?

(Edit: Actually, an intervalmatch is serious overkill here. You can just extract the date from the EventTime instead of intervalmatching. Not sure how I missed the obvious. Still, same question about why you don't want to do this.)