topic Show Value on Bar Chart (Conditional data value based on field selection) in QlikView

Show Value on Bar Chart (Conditional data value based on field selection)

IAMDV — Mon, 16 May 2011 16:23:18 GMT

Dear All,

I am using a stacked bar chart and I wanted to display the "Values on Data Points" - conditionally. If I select a value from a field the data points should show and if I dont select anything the chart should hide them.

I thought I can use the below expression in the (Sub expression - Show Value), however I had realised this works only for the line chart and does not work on bar chart. Here is the expression...

=IF( GetSelectedCount(MyField) > 0 , 0, 1)

Please can someone help me on this?

Original Requirement :

The original requirement is to show 100% across each hour and not 5.56%. I can't seem to figure how to get this done, so I thought I would hide 5.56% and show the values when there is selection.

Hope this is making sense.

Thanks in advance.

Cheers - DV

Show Value on Bar Chart (Conditional data value based on field selection)

erichshiino — Mon, 16 May 2011 16:27:29 GMT

Hi, DV.

going back to your original requirement, you can try to use the total on the expression.

In your case, it can be something like:

sum(value) / sum( total <TimeStamp> Value)

Hope it helps,

Erich

Re: Show Value on Bar Chart (Conditional data value based on field selection)

IAMDV — Mon, 16 May 2011 17:05:54 GMT

Erich - Thanks for quick reply. I really need to this to be done ASAP. Here is the actual expression which I am using...

=COUNT({$<Resp_ID = , Q_No ={'Q1'} >} Resp_ID)

And I am using two dimensions...

TimeStamp & Behaviour.

I hope this make sense... Please can you tell me on how to integrate the expression to show 100% for each hour or how to hide the data value % when there is no selection on Behaviour.

Thanks - DV

Re: Show Value on Bar Chart (Conditional data value based on field selection)

IAMDV — Mon, 16 May 2011 17:15:18 GMT

I had tried to use the below expression and it does not solve the issue...

=COUNT({$<Resp_ID = , Q_No ={'Q1'} >} TOTAL Resp_ID)

Thanks - DV

Re: Show Value on Bar Chart (Conditional data value based on field selection)

erichshiino — Mon, 16 May 2011 17:22:02 GMT

Since you need something faster, I will send this without really trying it before.

= dual ( IF( GetSelectedCount(MyField) > 0 , '' ,

COUNT({$<Resp_ID = , Q_No ={'Q1'} >} Resp_ID)/

COUNT({$<Resp_ID = , Q_No ={'Q1'} >} total<Timestamp> Resp_ID)),

COUNT({$<Resp_ID = , Q_No ={'Q1'} >} Resp_ID)/

COUNT({$<Resp_ID = , Q_No ={'Q1'} >} total<Timestamp> Resp_ID))

It may require adjustments to your fields names, etc

Hope it helps

Re: Show Value on Bar Chart (Conditional data value based on field selection)

IAMDV — Tue, 17 May 2011 08:38:13 GMT

Hi Erich,

Thank you for your help. Much appreciated. Below expression is what I needed. Can you please explain the use the dual function in the above expression?

I had stripped of dual and this just works the way I wanted. However I don't want miss out on your logic! so please can you explain in detail (if possible...)

Thanks in advance.

= IF( GetSelectedCount(Label_Text) > 0 ,

COUNT({$<Resp_ID = , Q_No ={'Q1'} >} Resp_ID) ,

COUNT({$<Resp_ID = , Q_No ={'Q1'} >} Resp_ID)/

COUNT({$<Resp_ID = , Q_No ={'Q1'} >} total <TimeStamp> Resp_ID))

Cheers - DV

Re: Show Value on Bar Chart (Conditional data value based on field selection)

erichshiino — Tue, 17 May 2011 10:54:53 GMT

Great you found a solution!

The dual function deals with the number representation on charts.

Each value can have a numeric real content and one string to represent.

So, in a bar chart, for example, the numeric value wil give you the size of the bar, and the string value as money.

For example, a date is basically a number. You can show any date as an integer, but I it formated to be represented as MMM/YYYY.

You can play with it and create a bar chart with bars of the size 1,2,3 and 4 and use the dual to show the opposite (4,3,2,1).

The syntax is dual( text, number)

I used dual to control the string representation of the calculation. So when the condition was fulfilled I would suppress the string, if not, I would present the result of the calculation.

If you prefer, I can send you an example later.

Regards,

Erich

Re: Show Value on Bar Chart (Conditional data value based on field selection)

IAMDV — Tue, 17 May 2011 11:48:17 GMT

Erich - Brilliant! I had just read the documentation on Dual Function. Your explanation is much simplier

I clearly understood the use of Dual expression. Please don't spend time mocking up an example, rather you use the same time for fellow user... helping them to solve their issue.

Many thanks for your help!

Cheers - DV