topic Multiple Number Format in Single Expression in QlikView

Multiple Number Format in Single Expression

Thu, 03 Sep 2009 22:03:21 GMT

Is it possible to format data in a column based on what the sub dimension is?

For example, I want the following data to

DVP	MetricType	CY	LY	% Diff
C. Parsons	Total Revenue	10000	9000	11%
C. Parsons	Discounts	-2000	-5000	-60.00%
C. Parsons	Write Offs	-500	-1000	-50.00%
C. Parsons	Sq Ft Occupancy	75.75	81.82	-7.42%
C. Parsons	Full Price Rentals	2581	0	-

formatted as

DVP	MetricType	CY	LY	% Diff
C. Parsons	Total Revenue	$10,000	$9,000	11.11%
C. Parsons	Discounts	($2,000)	($5,000)	-60.00%
C. Parsons	Write Offs	($500)	($1,000)	-50.00%
C. Parsons	Sq Ft Occupancy	75.75%	81.82%	-7.42%
C. Parsons	Full Price Rentals	2581	0	-

I was able to format properly by having each metric type as an individual expression, but in doing that I could not generate the % Diff column because that column is a delta of the CY and LY columns.

Multiple Number Format in Single Expression

Thu, 03 Sep 2009 23:03:09 GMT

I had a similar situation where I wanted one of the partial sums in a pivot table to be a percent while everything else was a number. I ended up cheating and concatenating the % sign to the end of the expression.

Something like:

If(MetricType='Sq Ft Occupancy', Exp & '%', Exp)

It also looks like you have different ways of signifying negative in there and also different rounding. You could probably still use something similar, but you'd have to get a little more creative to handle those two issues. For the negative on the percentage, you could use a conditional to put a minus in front if it was negative and then display the absolute value.

If you could make the two displays a little more alike (- for negatives on both), it could simplify things.

Multiple Number Format in Single Expression

Thu, 03 Sep 2009 23:11:16 GMT

I actually tried that in and it didn't work because I made another error when using that process.

In order to use the "custom" number formatting, you need to leave the number format setting at expression default. That was my mistake.

Thanks for giving me that push to figure it out.

Multiple Number Format in Single Expression

johnw — Fri, 04 Sep 2009 03:03:43 GMT

Rather than changing a number into text, I would simply set the formatting. For example (untested):

num(expression,pick(match(column
,'Some Number','Some Dollar Amount' ,'Some Percentage')
,'#,##0' ,'$#,##0.00;($#,##0.00)','#,##0.00%')

And in your case, it looks like you probably have different expressions for each, so you could do something like this instead (untested):

if(column='Some Number' ,num(some numeric expression ,'#,##0')
,if(column='Some Dollar Amount',num(some dollar expression ,'$#,##0.00;($#,##0.00)')
,if(column='Some Percentage' ,num(some percentage expression,'#,##0.00%'))))