Simulate an If statement using Set Analysis

Wed, 27 Oct 2010 18:54:26 GMT

I need to calculate something based on a criteria like this:

If Column A = 1 Then Sum the Amount where ProductGroup = A And Month<= Month selected

If ColumnA = 2 Then Sum the amount where ProductGroup = B And CostCenter = 1111 And Month = Month selected.

The sum part of the set analysis I know how to do, but my question is about the If ColumnA. How can I do that without using an IF?

Simulate an If statement using Set Analysis

johnw — Wed, 27 Oct 2010 20:04:31 GMT

Do the ColumnA checks with IFs (or a pick(ColumnA)). The IFs will only be evaluated once, not once per row, so there is no advantage to putting them inside of the set analysis. Putting them in the set analysis will probably just slow things down. But just for demonstration purposes, I believe this is how you'd do it:

sum({<ColumnA*={'1'},ProductGroup={'A'},Month={"<=$(=Month)"}>
+<ColumnA*={'2'},ProductGroup={'B'},CostCenter={'1111'}>} Amount)

Two things are going on here. First, there's the *= syntax, which says to intersect your selection with the value. In other words, for ColumnA*={'1'}, the set will just be '1' if '1' is selected, otherwise it will be an empty set. Second, we union two sets together with the + operator. This is functioning as an OR, basically. The net effect of both should be the same as your IFs. Better to use IFs, though.

topic Simulate an If statement using Set Analysis in QlikView

Simulate an If statement using Set Analysis

Simulate an If statement using Set Analysis