topic Problem with Set Analisys formula in QlikView

Problem with Set Analisys formula

Mon, 08 Feb 2010 12:39:04 GMT

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

Problem with Set Analisys formula

Miguel_Angel_Baeyens — Mon, 08 Feb 2010 13:19:39 GMT

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.

Problem with Set Analisys formula

Mon, 08 Feb 2010 13:27:24 GMT

Hi Adelaida,

the set analysis formula should look like this:

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

Milda

Problem with Set Analisys formula

Mon, 08 Feb 2010 13:45:52 GMT

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