percents and money in the same column

[#1]

Other than using lengthy nested IF statements in expressions are their easier ways to have a single column that contains both percent values and integers in various rows?

[johnw]

While I can't say for sure, I can't imagine how there even could be an easier way. If you're using a lengthy IF statement to decide what to display in a column, how is QlikView supposed to just know what to display there without all of that information? And if it needs all of that information, I'm not sure how much easier it could get to provide it than simply writing an IF statement.

That said, if you want to post a sample file with sample data and a sample IF statement, I can take a look and see if I can come up with a better approach, or can simply confirm that you're doing it as best I know how.

[#3]

Lately I have been using alot of pick(match syntax to handle multiple calculations in a single expression. This is less coding than using IF's and i have done some experimenting with dynamically building the entire expression. I use this along with a island "calculation dimension" to gain alot of flexibility in a pivot table.

Following is a sample expression:

Pick(Match(CalcGroup,'Actual','Budget','Variance','Variance %'),

num(Sum({$<Year={$(=max(total Year))} >} AMOUNT),'$#,##0'),

num(Sum({$<Year={$(=max(total Year))}>} BUDGET_AMOUNT),'$#,##0'),

Num(Sum({$<Year={$(=max(total Year))}>} AMOUNT)
-
Sum({$<Year={$(=max(total Year))} >} BUDGET_AMOUNT),'$#,##0;($#,##0)'),

Num(Sum({$<Year={$(=max(total Year))} >} AMOUNT)
/
Sum({$<Year={$(=max(total Year))} >} BUDGET_AMOUNT)-1,'##0.0%')
)

Similarly I will often create a calc group that inludes "Current Year','Prior Year', 'Variance','Variance %' so that I can do a single table with a year over year reporting accross multiple Measures.

Regards,

Greg Farrell

[johnw]

Well, you might be able to write these as separate expressions and remove the CalcGroup dimension. But I believe I've proposed exactly the structure you're now using in response to other people's requirements, so I'm guessing you simply have one of those requirements where using separate expressions just doesn't do the job.

If so, you still may be able to do a few things to simplify the expression, even if it won't fundamentally change or improve your approach. The $ to indicate the current set is optional and can be dropped. Since the set analysis expressions for the two halves of your Variance are the same and you're merely subtracting, you can combine them:

Num(Sum({<Year={$(=max(total Year))}>} AMOUNT - BUDGET_AMOUNT),'$#,##0;($#,##0)'),

You could create a table like this:

[Formats]:
LOAD * INLINE [
CalcGroup, Format
Actual, $#,##0
Budget, $#,##0
Variance, $#,##0;($#,##0)
Variance %, ##0.0%
];

Hmmm, need another delimiter than comma, but I'm too lazy to fix it. Then handle the numeric formatting by wrapping the whole expression in a num(...,Format) rather than having to do a numeric format for each line. Similarly, you might be able to move the logic for getting the most recent year into a calculated dimension. Calculated dimensions seem to be really slow, though, and that wouldn't work for your table with both current and prior years in it. Not sure if you'd prefer it, but you could probably put the sub expressions in a table as well:

[Calcs]:
LOAD * INLINE [
CalcGroup, Format, Calculation
Actual, $#,##0 sum({<Year={$(=max(total Year))}>} AMOUNT)
Budget, $#,##0 sum({<Year={$(=max(total Year))}>} BUDGET_AMOUNT)
Variance, $#,##0;($#,##0) sum({<Year={$(=max(total Year))}>} AMOUNT - BUDGET_AMOUNT)
Variance %, ##0.0% sum({<Year={$(=max(total Year))}>} AMOUNT) / sum({<Year={$(=max(total Year))}>} BUDGETAMOUNT)
];

Again, need to fix the delimiters to make it work. And I forget how to make QlikView actually DO the calculation instead of just returning the text value, but I'm pretty sure there's a way. And you'd have to make "Variance" when comparing current year to prior year be a different CalcGroup.

[#5]

This isn't a true calculated dimension but an "island" dimension created through an inline load statement as you demonstrated.

The set logic was not pertinent to my example. I should have left if out.

I see where you are headed in adding additional components of the calc in the CalcDimension table itself.

I believe through using concat you should be able to manufacter the sample 4 expressions into a single variable and concat the CalcDimension into a separate variable to be used in the match() function...

so you'd end up with

=pick(match($(vCalcDimensions)), $(vCalcExpressions))

as your expression.

I'll save that one for later.

I'll save that one for a later date.

[johnw]

I was thinking that instead of generating the pick(match()), you could just have CalcGroup as your dimension, and $(=Calculation) as your expression. Except that I think you'd still only get the text that way. But there's probably some way to tell QlikView "I want you to evaluate Calculation and return the result, not the text".

[#7]

I tried everything i could think of earlier to make the calc truly dynamic and could not get it to be interpreted correctly. Pick match was the next best option.

I haven't given up totally but a truly dynamic expression seems to have limitations.