28 Replies Latest reply: Jun 13, 2016 7:32 AM by Yeo Poh sai

How to make all my bar chart same height , so that i can view the % changes over the year

Hi All

mY cHART 1 now is correct , but i am not able to make the bar all same height.

Hope some one can advise me.

Paul

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

my QV Doc

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi paul,

Look at this:

Regards!

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Manual

Thank you very much , it work .

I notice you create a expression below for display rEVENUE amount :-

Dual(Sum({<rEVENUE={"rEVENUE"}>}Amount*1),Sum({<rEVENUE={"rEVENUE"}>}Amount*1)

/

(Sum({<rEVENUE={"rEVENUE"}>}Amount*1)+Sum({<eXP={"eXP"}>}Amount*-1)))

May i know what it the above expression does ? i mean what does the Dual do to the whole expression ?  pls share with me.

Paul

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Paul,

Dual function has two parameters, text and num, Dual(Text,Num). It shows the text on chart, but gives the value to the bar.

Dual(Amount value,% of Amount respect Sum(Amount+Exp))

On Qlik help:

dual( s , x )

Forced association of an arbitrary string representation s with a given number representation x. In QlikView, when several data items read into one field have different string representations but the same valid number representation, they will all share the first string representation encountered. This function can be used in scripts and chart expressions.

In scripting, the dual function is typically used early in the script, before other data is read into the field concerned, in order to create that first string representation, which will be shown in list boxes etc.

Note!
If a dual value is too large to fit in a field object, it will be represented by ## and not truncated with ... like a string.

Regards.

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Manuel

Thank you very much for your explain on how to use dual .I have remove the Dual , because i don't want it display the amount , instead i try to display the % on the bar , Any idea ?

Paul

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Paul

Change dual function by % calculation. On number tab select number format in percent.

Regards.

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Manuel

Many thank it work fine,.

Paul

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Manuel

After i convert the number to % , now the % return 29% , it should be 42%. Any idea where go wrong ?

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Paul,

Exp + Revenue = 1946290285.46

Exp = 571730140.64

Percentage is 29%, it is ok.

Regards

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Manuel

You are right base ,for  Exp / (Exp + Revenue)

But I need to compute  Exp / Revenue not Exp / (Exp + Revenue)

I try to modify the expression , the Bar become some short some long.

Paul

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Paul,

Then your expression must be Sum({<eXP={"eXP"}>}Amount*-1)/Sum({<rEVENUE={"rEVENUE"}>}Amount*1)

Regards

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Manuel

I Have try to look at your 2 expression , one is take care of create the rEVENUE amount , and another on eXP

And in order to make the bar same height , you compute both amount with total sum of rEVENUE and eXP

MY imagination to make the ratio base on eXP vs REVENUE by using eXP/rEVENUE is should be very simple , but no matter which way I try I still cannot get the % I want.

my last request to you , it is possible you show me how you do it by using my QV doc ?

PAul

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Paul,

Do you wanna this?

Regards

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Manuel

Many thank for your help , it work fine now. After i check your profile , you are high post in QV , no wonder your expression is so powerful , that i am still unable to figure out how you make it work.

Paul

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Paul,

I'm dividing x versus z, and the other expression rest 1 the result of this division. If you put the same name you've given the above expression, on the same chart, collect the result of that expression for each dimension value.

Regards!

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Manuel

Now i understand how it work now . I also spot some issue , i manage to change the following :-

1) Convert the eXP and rEVENUE to YTD amount instead of now is sum amount, So that i manage to make it able to compare the ratio by company.

2) I also remove the IF , by change the eXP*1 to eXP *1

Many Thank and i can apply the same approach for other ratio. So all chart will look perfect.

Paul

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

One more point is , i am able to make it work in QS.

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Manuel

After success in create the stack chart in QV , i try to do the same for QS.

Chart 1 work fine . Now it display eXP % , I like to make it stack chart , which display rEVENUE and eXP in one bar , total amount add up equal to 100%.

I try to create chart 2 , using the same approach, but it does not work.

Hope some one can advise me,

Paul

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi paul,

On QS you can't show values by column on stacked bar chart.

Regards.

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Manuel

Paul

Sent from my iPhone

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Paul,

Might be o/p data is different so that if o/p came like that.

It's depends on the Year wise o/p

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Anil

I don't understand what you try to tell me ? By the way Manuel solution work fine for me , just that i don't understand the expression , and how he make it work.

Paul

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Paul,

Time G_D.

I want to know one thing. When ever mouse over goes to the Chart 1 some pop-up came right.

So do you know how to increase that pop up font size, If you know can you please let me know where i need to change the font size of that Pop Up

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Anil

You can click on the chart property , and click on Fonts Tab , and go to right size top and change the fonts size from 12 to 20 , it will increase the fonts size.

Paul

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

You can do it in QlikSense with this:

Expr1: IF(Sum({<eXP={"eXP"}>}Amount*-1)/Sum({<rEVENUE={"rEVENUE"}>}Amount*1)>1,1,Sum({<eXP={"eXP"}>}Amount*-1)/Sum({<rEVENUE={"rEVENUE"}>}Amount*1))

Expr2: 1 - IF(Sum({<eXP={"eXP"}>}Amount*-1)/Sum({<rEVENUE={"rEVENUE"}>}Amount*1)>1,1,Sum({<eXP={"eXP"}>}Amount*-1)/Sum({<rEVENUE={"rEVENUE"}>}Amount*1))

Change name of first expression on second expression by expression 1 content.

If you want to do it YTD, make it on Set Analysis: QlikView App: Simple Year To Date Example (Set Analysis)

Regards!

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Manuel

I already manage to create the chart using QS with SET expression and it work fine now.

By the way may i know why you need to make use of IF ?

See my expression with out using the IF , i just change the eXP*1 to eXP*-1 in order to remove the negative sign.

Paul

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

I use IF because if A/B is greather than 1, I will get 1, else I will get the result of A/B.

With that check I discard that have negative values

Regards

• Re: How to make all my bar chart same height , so that i can view the % changes over the year

Hi Manuel

Good idea . This way I can plot those ratio with + and - %

Paul

Sent from my iPhone