SUM If Alternative

[#1]

How can I formulate a count if in Qlikview for this formula?

IF(AccountingPeriod.EndDate >= SubscriptionCancellationDate AND AccountingPeriod.StartDate <= SubscriptionCancellationDate, 1, 0)

[Anil_Babu_Samineni]

Perhaps This

Count({<AccountingPeriod = {'>=EndDate <=StartDate}>}SubscriptionCancellationDate)

OR

Can you please elaborate more what you want to achieve?

[ToniKautto]

The set expression in Anil's example will limit the data set for the aggregation, which I think might give you an unexpected or incorrect result.

I would suggest that you try a Count() expression where the If() statement is inside the aggregation function. This means that when your condition is fulfilled 1 is added to the aggregation. When the condition is not fulfilled Null is added to the result, since the Else expression is undefined. Count(Null()) is equal to zero, to this means that the expression will return the number or 1's returned by the If() statement.

Count(If(AccountingPeriod.EndDate >= SubscriptionCancellationDate AND AccountingPeriod.StartDate <= SubscriptionCancellationDate, 1))

As a side note the same result can be retrieved by using Sum(), since it will simply add all the 1's.

Sum(If(AccountingPeriod.EndDate >= SubscriptionCancellationDate AND AccountingPeriod.StartDate <= SubscriptionCancellationDate, 1))