Problem with Set Analisys formula

[#1]

Hello everybody,

I have a problem with set analysis formula. I try to calculate the valid accounts at month end. I have a calendar table that has a field, MontEnd, calculated as

IF(DayStart(TempDate) = DayStart(MonthEnd(TempDate)),MonthEnd(TempDate)) AS MonthEnd

If I write the stock as

=Count(distinct IF(MonthEnd=date(MonthEnd(AddMonths(Today(),-1))),ACCOUNT))

I have the right values. If I try to write the same thing with set analysis formula below I have 0 for all dimensions.
=Count({$<MonthEnd={$(#=date(MonthEnd(AddMonths(Today(),-1))))}>} DISTINCT ACCOUNT)

Please, can you tell me the write syntax of set analysis in this case?

Thank you all very much!

Best regards,

Adelaida

[Miguel_Angel_Baeyens]

Hello Adelaida,

Syntax seems to be ok for me. But I may change

$(#=date

for

$(=date

as I'm not sure about comparing a datefield with date format (as you are getting in your load script) stored in Monthend field with the numeric value returned by the preceding "#" in your set analysis.

By the way, I would suggest you to change the name of the field MonthEnd, as this is, as well, the name of a function, which can mislead QlikView when operating (set analysis or whatever) try to distinguish beyween function name and field name. This may be likely affecting your set analysis.

Regards.

[#3 · Accepted solution]

Hi Adelaida,

the set analysis formula should look like this:

=Count({$<MonthEnd={$(='"'&date(MonthEnd(AddMonths(Today(),-1)))&'"')}>} DISTINCT ACCOUNT)

Milda

[#4]

Thank you very much Milda!! It works!!!!