topic Re: Y-axis (expression) formatting in QlikView

Y-axis (expression) formatting

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a line chart that looks like this:

How do you format the Y-axis to be of money type? Example, I want to show $800,000, $600,00 and etc.

I've looked through all the tabs for the line chart and especially the Axis tab -> Expression Axes. I don't see anything

relevant on changing y-axis to money.

Can someone help?

Thank you

Re: Y-axis (expression) formatting

Mon, 31 Oct 2016 14:48:48 GMT

I guess you can get the $ symbol at the top of the axis, but not against each value.

Try reading from this - In bar chart $ symbol can't be prefixed to the numbers in y-axis?

Re: Y-axis (expression) formatting

MK9885 — Mon, 31 Oct 2016 14:53:29 GMT

Check values on data points in expression tab for your Y Axis and edit the expression to

Num(yourexpression), '$#,##0') or use Num(yourexpression, /100000 , '$#,##0 K') as you've values in thousands, showing a K symbol will be more good or there are chances that values may overlap each other on line plot.

Can you show what expression you using for y-axis?

Re: Y-axis (expression) formatting

Anonymous — Mon, 31 Oct 2016 15:00:12 GMT

Actually I had already tried it before posting. I tried with "NUM(sum(Total_btts), '$#,##0;(#,##0)')" but didn't work.

I just entered what you suggested, it didn't work and the y-axis actually didn't change.

Re: Y-axis (expression) formatting

Anonymous — Mon, 31 Oct 2016 15:02:06 GMT

There are 2 ways to do it:

1)Change the settings in the Number tab of the chart properties to Money and add $ in the Symbol box.

This adds $ sign on top of the y-axis and in the values on the data points.

2)Add value in the "Static Step" option under the Axes tab, this will internally take the option set in the Number tab ,if you select "Money" then this will show it in that format. Try putting values like 1000 under the static step ,i haven't tried it for your data set but it should work

Re: Y-axis (expression) formatting

Mon, 31 Oct 2016 15:04:41 GMT

Arvind - I doubt that.

Re: Y-axis (expression) formatting

MK9885 — Mon, 31 Oct 2016 15:08:59 GMT

I do not think Y-Axis change, to see the change you have to check or tick the box 'Values on Data Points' in Expression tab.

The values will be visible for you on line, there you can see $ symbol. But on Y axis it will show you plain numbers.

Re: Y-axis (expression) formatting

Mon, 31 Oct 2016 15:11:54 GMT

True !!

Re: Y-axis (expression) formatting

Anonymous — Mon, 31 Oct 2016 15:16:05 GMT

Hi arvind654,

Thanks for your replies. Yes, you were right about checking "Values on data points". I do see the $.

However since I need to show 12 months, the chart gets crowded with these data points showns especially when

the values are large. In addition, I need to show multiple years as well. So this will not be very attractive to the users and it isn't what I was asking. I've looked on the Web and it doesn't appear you can do what I'm asking in QV.

Re: Y-axis (expression) formatting

MK9885 — Mon, 31 Oct 2016 15:21:31 GMT

use this instead to shorten the values and give it a symbol.

NUM(sum(Total_btts), /100000 , '$#,##0 K')

As we all know K is a symbol for thousand.

Re: Y-axis (expression) formatting

Anonymous — Mon, 31 Oct 2016 15:51:47 GMT

There are 2 ways to do it:

1)Change the settings in the Number tab of the chart properties to Money and add $ in the Symbol box.

This adds $ sign on top of the y-axis and in the values on the data points.

Re: Y-axis (expression) formatting

Anonymous — Mon, 31 Oct 2016 16:32:39 GMT

Vivek,

Good suggestion. But it only works for certain data scenarios (at least for the data I've). When I pick different years,

the $ may or may not display because the data points are different. The "static step", as the name implies, I am guess

it only works under certain range of values

Re: Y-axis (expression) formatting

Anonymous — Mon, 31 Oct 2016 16:48:17 GMT

you can write a dynamic formula too ,it need not be static

Re: Y-axis (expression) formatting

MK9885 — Mon, 31 Oct 2016 17:14:44 GMT

It worked fine for me when I used /1000000 , '$#,##0 M' for Million.

But changing the Y axis all values to $ is not possible. Only possible way to show is as data points.

Re: Y-axis (expression) formatting

Anonymous — Mon, 31 Oct 2016 17:19:40 GMT

Luu,

I recommend you to go thorough the below link where kent has written a dynamic code snippet for the same.

In bar chart $ symbol can't be prefixed to the numbers in y-axis?

Re: Y-axis (expression) formatting

Anonymous — Mon, 31 Oct 2016 17:46:21 GMT

OK, thanks. I will look and post an update later.

Re: Y-axis (expression) formatting

Anonymous — Mon, 31 Oct 2016 17:51:27 GMT

Also,take a look at the below link too

Currency and Other Symbols on the Y-Axis | Qlikview Cookbook