Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Adelaida,
the set analysis formula should look like this:
=Count({$<MonthEnd={$(='"'&date(MonthEnd(AddMonths(Today(),-1)))&'"')}>} DISTINCT ACCOUNT)
Milda
Hello Adelaida,
Syntax seems to be ok for me. But I may change
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.$(=date
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.
Hi Adelaida,
the set analysis formula should look like this:
=Count({$<MonthEnd={$(='"'&date(MonthEnd(AddMonths(Today(),-1)))&'"')}>} DISTINCT ACCOUNT)
Milda
Thank you very much Milda!! It works!!!!