Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

SUM If Alternative

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

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

2 Replies

Re: SUM If Alternative

Perhaps This

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

OR

Can you please elaborate more what you want to achieve?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Employee
Employee

Re: SUM If Alternative

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