SUM If Alternative

Wed, 25 Nov 2020 16:16:04 GMT

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

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

Re: SUM If Alternative

Anil_Babu_Samineni — Sat, 27 Aug 2016 02:17:38 GMT

Perhaps This

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

Can you please elaborate more what you want to achieve?

Re: SUM If Alternative

ToniKautto — Sat, 27 Aug 2016 04:28:27 GMT

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))

topic Re: SUM If Alternative in QlikView

SUM If Alternative

Re: SUM If Alternative

Re: SUM If Alternative