29 Replies Latest reply: Apr 18, 2016 8:30 AM by Avinash R

Showing totals when hovering

Hi

I know you can show totals when hovering on a bar chart. But my totals on my expressions are all divided by millions, so it you hover ot shows 121 for 121435000, so how do I show the full total while hovering?

• Re: Showing totals when hovering

Hi,

have a look at the link below.

mouseover label

Regards,

Kaushik Solanki

• Re: Showing totals when hovering

This works well if there is only one bar chart, but say you have more than one bar, how do you link the popup label to the correct bar

• Re: Showing totals when hovering

hi,

u can use

='urtext ' &Chr(10)&'Expresion'

• Re: Showing totals when hovering

Not making sense to me, see the other reply I did with the Word document

• Re: Showing totals when hovering

Did you try with Text as a PopUp??

Properties -> Expression -> Text as a PopUp

• Re: Showing totals when hovering

I have done this it works fine for values over a million,Most of my bars are more than a million, so I have the Y Axis as million values.

See attached document

The code for example is with or without the /1000000

if(MonthName=1,sum({\$<MonthName=,AccountType = {P},MonthNum = {1},CapitalTypeShort = {'Intangible'}>}BudgetDistributionCalculation + BudgetMovementMonthly),

if(MonthName=2,sum({\$<MonthName=,AccountType = {P},MonthNum = {1,2},CapitalTypeShort = {'Intangible'}>}BudgetDistributionCalculation + BudgetMovementMonthly),

if(MonthName=3,sum({\$<MonthName=,AccountType = {P},MonthNum = {1,2,3},CapitalTypeShort = {'Intangible'}>}BudgetDistributionCalculation + BudgetMovementMonthly),

if(MonthName=4,sum({\$<MonthName=,AccountType = {P},MonthNum = {1,2,3,4},CapitalTypeShort = {'Intangible'}>}BudgetDistributionCalculation + BudgetMovementMonthly),

if(MonthName=5,sum({\$<MonthName=,AccountType = {P},MonthNum = {1,2,3,4,5},CapitalTypeShort = {'Intangible'}>}BudgetDistributionCalculation + BudgetMovementMonthly),

if(MonthName=6,sum({\$<MonthName=,AccountType = {P},MonthNum = {1,2,3,4,5,6},CapitalTypeShort = {'Intangible'}>}BudgetDistributionCalculation + BudgetMovementMonthly),

if(MonthName=7,sum({\$<MonthName=,AccountType = {P},MonthNum = {1,2,3,4,5,6,7},CapitalTypeShort = {'Intangible'}>}BudgetDistributionCalculation + BudgetMovementMonthly),

if(MonthName=8,sum({\$<MonthName=,AccountType = {P},MonthNum = {1,2,3,4,5,6,7,8},CapitalTypeShort = {'Intangible'}>}BudgetDistributionCalculation + BudgetMovementMonthly),

if(MonthName=9,sum({\$<MonthName=,AccountType = {P},MonthNum = {1,2,3,4,5,6,7,8,9},CapitalTypeShort = {'Intangible'}>}BudgetDistributionCalculation + BudgetMovementMonthly),

if(MonthName=10,sum({\$<MonthName=,AccountType = {P},MonthNum = {1,2,3,4,5,6,7,8,9,10},CapitalTypeShort = {'Intangible'}>}BudgetDistributionCalculation + BudgetMovementMonthly),

if(MonthName=11,sum({\$<MonthName=,AccountType = {P},MonthNum = {1,2,3,4,5,6,7,8,9,10,11},CapitalTypeShort = {'Intangible'}>}BudgetDistributionCalculation + BudgetMovementMonthly),

if(MonthName=12,sum({\$<MonthName=,AccountType = {P},MonthNum = {1,2,3,4,5,6,7,8,9,10,11,12},CapitalTypeShort = {'Intangible'}>}BudgetDistributionCalculation + BudgetMovementMonthly),

sum({\$<MonthName=,AccountType = {P},MonthNum = {1},CapitalTypeShort = {'Intangible'}>}BudgetDistributionCalculation + BudgetMovementMonthly)

))))))))))))/1000000

• Re: Showing totals when hovering

Try with the dual() function

How to use- Dual()

• Re: Showing totals when hovering

Not sure I quite understand where you want me to use it. I already use the Dual Function with Month name and Month number combined. Do you mean having a dual with the million rounded and the full value? We have to use it as our Financial Month are June = 1, July = 2, so I am forced to use it for ordering purposes as well, so I can order months by numbers.

My issue is more related to the following

When values are more than a million, I have the Y-axis showing the values in millions, but when I hover it shows the full value

But when the values are less than a million, I still need the Y axis to show the values in millions, for example 250 000 as 0.25 and when hovering it will show 250 000

• Re: Showing totals when hovering

I think your formatting the number in the Number tab? if so this situation is common , try to handle the format in the Expression this will be always consistent...

Do you mean having a dual with the million rounded and the full value?

Yes , I asked you to use dual function  for this type of formatting

• Re: Showing totals when hovering

But how do I differentiate between the two, when using it, its easy enough with a text and a number field in the Dual, how would it work with two number, for example Dual(250000,0.25). Am I understanding you correctly?

• Re: Showing totals when hovering

try like this

DUAL( your_expression, your_expression/1000000)

• Re: Showing totals when hovering

Can you do it on the actual expression inside a chart or in the load statement, there are some really small values involved in single rows, so would you out lose a lot of data, example 500 would be 0.000500, Qlikview would keep these values?

Still the remaining issue is, seeing as they are two numeric values, how do I differentiate between they two in a chart, how do I select it I want to use the million value or the full value?

Hope I am making sense

• Re: Showing totals when hovering

Qlikview will  not ignore any values

dual(expression1,expression2) functions like this

In dual expression1 will be considered for your hover display but for the chart the value of the expression2 is considered .

EG: if you have dual(Month_name,Month_value) then

while building the chart your Y Axis will be generated according to the Month_value but will showing it in the hover the value of Month_name is displayed .

In your case dual() function will help you to set the Y axis always set to M and it the hover it will show the actual values

hope this makes better

• Re: Showing totals when hovering

It helps, but I am still unsure how you deal with the two numeric values, how do I code it to use the one for the Hovering and one for the Y Axis? Do you maybe have an example? I have only used Dual for a text and numeric before, never with two numeric values.

Sorry for all the questions, just can't get my head around the two numeric values, maybe I am just overcomplicating it

• Re: Showing totals when hovering

nothing to worry with the 2 numeric values dual will handle it.

how do I code it to use the one for the Hovering and one for the Y Axis?

dual(expression,expression/10000000)

here expression will be considered for the hover and expression/10000000 will be set for the Axis you need to worry on this part try with the expression

• Re: Showing totals when hovering

Will it autoselect which one to use for Y axis and which to use for hovering?

• Re: Showing totals when hovering

yes , 1st part is for hovering and 2 part is for the y axis

• Re: Showing totals when hovering

Will try this and let you know. Thanks so much for the help sofar

• Re: Showing totals when hovering

Works for the charts as far as have tested but for line charts it is not working. One option is to have an extra field which is not dual for the line charts but then still I have the issue with the values less than a million

• Re: Showing totals when hovering

As per i know for line chart the values will be shown in the end points i.e move mouse near the value joining points it will display the values ....if have got the answer please mark the correct answer and close this thread

• Re: Showing totals when hovering

Tested with the bar charts of less than million, those with the millions is still working, it shows the amount when hovering, on those less than a million it shows the bars correctly, but on hovering it does not give the values

• Re: Showing totals when hovering

could you please share the snapshots ?? so that it will helpful for me understand the issues

• Re: Showing totals when hovering

Is a word file ok, you have word 2016 or should I just save it in a pervious version?

• Re: Showing totals when hovering

Here is what I get, before and after, see attached word file. I saved it in an old version

• Re: Showing totals when hovering

Did you manages to find anything from the screenshots I sent you? The first value in the dual is correct, its the value used for hovering. The second value is for the Y axis. wish I could send you the model, this is just not making sense to me, it just looks inconsitent to me. Basically everything was working fine on all lines charts and bar charts except for these under a million in the currency

• Re: Showing totals when hovering

I did not understand the exact problem ....in the last chart your not able to see the values ....could you please share the dimensions and expression that are used in that chart ??

• Re: Showing totals when hovering

Dimension is = if(Match(Quarter,'Q1', 'Q2', 'Q3', 'Q4'),Quarter,null())

Expression 1 Budget is

sum({\$<MonthName=,AccountType = {P}, CapitalTypeShort = {'Intangible'}>}BudgetDistributionCalculation + BudgetMovementMonthly)

Expression 2 Actual is

sum({\$<MonthName=,AccountType = {P}, CapitalTypeShort = {'Intangible'}>}Actual)

• Re: Showing totals when hovering

here is the issue

BudgetDistributionCalculation +BudgetMovementMonthly

you cannot add two or one dual fields with other fields  directly because dual is a combination of two fields 1st part with the display data and the 2 part for the actual calculation ..you in you case the data which is divided by 1000000 is getting add and hence your not able to see the right data ...try like this

create 2 different field for each i.e one with dual and another without dual

like

,Dual(BudgetDistributionCalculation, BudgetDistributionCalculation/1000000) as Dual_BudgetDistributionCalculation

,BudgetDistributionCalculation
,
Dual(BudgetMovementMonthly,BudgetMovementMonthly/1000000) as Dual_BudgetMovementMonthly

,BudgetMovementMonthly

now in the front end expression try like this

Dual(sum({\$<MonthName=,AccountType = {P}, CapitalTypeShort = {'Intangible'}>}BudgetDistributionCalculation +BudgetMovementMonthly),sum({\$<MonthName=,AccountType = {P}, CapitalTypeShort = {'Intangible'}>}BudgetDistributionCalculation +BudgetMovementMonthly)/1000000)

this should work

• Re: Showing totals when hovering

Just remember I have more than one bar in a chart. I saw some examples where its easy with only one bar i a chart adding an extra field. I also have line charts where I do the hovering, but it should be the same principle.

As said before I have got it working with my charts with values more than a million