topic Conditional expression number formatting in QlikView

Conditional expression number formatting

Mon, 01 Dec 2014 20:59:01 GMT

Hi everyone,

My chart has a conditional expression, that is really two different expressions. The expression used depends on setting a variable elsewhere. It looks something like this:

=if(vExpression='Total Spending',num(sum(Amounts),'$#,###,###'),

if(vExpression='Inventory',sum(Inventory),

))

I have enabled Values on Data Points, and I want the values to be formatted as $ for sum(Amounts), and as number for sum(Inventory). As you can see, I have tried to set the formatting in the expression, and I have set Number Format Settings in the Number tab to 'Expression Default', but this has not worked. Does anyone know of a solution?

Thanks

Re: Conditional expression number formatting

maxgro — Mon, 01 Dec 2014 21:24:53 GMT

maybe

=if('$(vExpression)'=......

Re: Conditional expression number formatting

Mon, 01 Dec 2014 21:27:55 GMT

Didn't work!

Re: Conditional expression number formatting

ecolomer — Mon, 01 Dec 2014 21:29:40 GMT

Can you try this:

= if($(vExpression)= ...

Re: Conditional expression number formatting

Mon, 01 Dec 2014 21:38:50 GMT

Didn't work, chart changes to 'No data to display'

Re: Conditional expression number formatting

maxgro — Mon, 01 Dec 2014 22:12:49 GMT

if possible, post a small example of your Qlik doc

Re: Conditional expression number formatting

Anonymous — Mon, 01 Dec 2014 22:28:29 GMT

A bit of a guess but how about converting the output to text, something like this :

=if(vExpression='Total Spending',text(num(sum(Amounts),'$#,###,###'))

if(vExpression='Inventory',text(sum(Inventory)),

))

Re: Conditional expression number formatting

Anonymous — Tue, 02 Dec 2014 03:03:37 GMT

Hi,

Crete a variable using below exression

vTest=sum(if($(vExpression)='Total Spending',Amounts,

if($(vExpression)='Inventory',Inventory)))

then use thebelow condition in you calculaiton

=if($(vExpression)='Total Spending',num($(vTest),'$#,###,###')

if($(vExpression)='Inventory',$(vTest)

))

Hope this helps. Pleas check for any missing braces.

BR,

chinna

Re: Conditional expression number formatting

jonathandienst — Tue, 02 Dec 2014 05:27:03 GMT

Your expression looks OK, except that the variable may need to be enclosed in quotes and a $ expansion (depending on how the variable is defined), and you need to make sure that the format on the Number tab is set to 'Expression Default'. Otherwise the setting in the Number tab overrides the format in your expression.

Then you may need to explicitly format the second sum (eg num(sum(Inventory), '# ##0') )

HTH

Jonathan

Re: Conditional expression number formatting

Tue, 02 Dec 2014 17:47:30 GMT

Thank you all for your responses.

Unfortunately, both Bill and Chinna's solutions resulted in 'No Data to Display'. And thank you Jonathan, I made sure that "expression default" was selected.

I will try to post a scrambled copy of my QVW soon. In the meantime, thank you all for your helpful responses.

Re: Conditional expression number formatting

Wed, 22 Apr 2015 09:57:43 GMT

Did you ever manage to find a way to get this to work? I am having the same problem now and wondering if there is a way to sort it out.

Thanks