topic Set Analysis little issue in QlikView

Set Analysis little issue

alec1982 — Thu, 06 Mar 2014 15:47:26 GMT

Hi Guys,

I am trying to use the following formula within a bar chart to get an average

=Sum({1<[Type]={'A'}>}[Amount])/Sum({1}B)

The Dimension is C.

I need to get a straight line of average across the bar chart. Not sure what I am doing wrong here.. I've done this hundred times before with no issue.

The formula above returns a zigzag line that moves with the bars..

Let me know your thoughts and thanks in advance.

Best,

Alec

Re: Set Analysis little issue

MK_QSL — Thu, 06 Mar 2014 15:52:00 GMT

SUM({<Type = {'A'}>}Amount)/SUM(B)

Re: Set Analysis little issue

alexandros17 — Thu, 06 Mar 2014 15:54:45 GMT

The expression you have written generates a different value for each value of dimension (1 means that is valid not depending by selections ...)

So ... what kind of average do you need really?

Re: Set Analysis little issue

alec1982 — Thu, 06 Mar 2014 16:00:29 GMT

I need to have one value across all dimensions. for example if you put the above formula in a text box it returns $1000. I need a line of $1000 to show across all the dimensions.

Re: Set Analysis little issue

PrashantSangle — Thu, 06 Mar 2014 16:06:00 GMT

Hi,

you can implement your expression as Reference line.

You can achieve this chart->properties->Presentation->Reference Line -Expression.

this will help you.

Re: Set Analysis little issue

Anonymous — Thu, 06 Mar 2014 16:07:17 GMT

In "Presentation" tab of the chart properties, add this expression in "Reference Lines" instead of adding it in "Expressions" tab. that will generate a straight reference line with the value you need. Is that what you are looking for?

Re: Set Analysis little issue

Thu, 06 Mar 2014 16:07:44 GMT

create a variable with that expression with '=' sign and use it instead. if ref line is not ur alternative.

Re: Set Analysis little issue

MK_QSL — Thu, 06 Mar 2014 16:11:08 GMT

Can you provide sample data?

Re: Set Analysis little issue

alec1982 — Thu, 06 Mar 2014 16:12:49 GMT

Hi Can you please give me an example.. I am getting an error.

The reference line works but I need to add it in an expression so I can see it in the legend.

Thxs,

Alec

Re: Set Analysis little issue

alec1982 — Thu, 06 Mar 2014 16:36:06 GMT

Simply any table with B, Type and C columns where C is a state and Type has multiple types and B is the amount.

My PC wont let me attached QVWs..

Thxs,

Alec

Re: Set Analysis little issue

MK_QSL — Thu, 06 Mar 2014 16:43:07 GMT

Dimension = C

=Sum({1<[Type]={'A'}>}[Amount])/Sum({1}B)

Let me clarify...

B = amount

Amount = Amount (Is B and Amount is different?, sorry for my stupid question, my intention is to help you..)

C = State

Type = any Type (i.e. Dimension)

If you give us three four lines data for above dimensions and your end results, would better ...!

Re: Set Analysis little issue

Brice-SACCUCCI — Thu, 06 Mar 2014 16:44:49 GMT

What about

=Sum({<[Type]={'A'}>}[Amount])/ Sum ({1}B)

Re: Set Analysis little issue

alec1982 — Thu, 06 Mar 2014 16:50:55 GMT

Sure, thank you for your help

C Type Amount Count

GA A 200 25

GA B 250 25

CL A 150 20

NC B 350 35

NY B 175 45

=Sum({1<[Type]={'B'}>}[Amount])/Sum({1}Count)

Re: Set Analysis little issue

alec1982 — Thu, 06 Mar 2014 16:52:10 GMT

this returns different value for each bar

Re: Set Analysis little issue

MK_QSL — Thu, 06 Mar 2014 17:15:06 GMT

I am confused now.

You said B is an amount and here B is a Type..!!

Re: Set Analysis little issue

MK_QSL — Thu, 06 Mar 2014 17:17:46 GMT

Try This...

=SUM({1<Type = {'A'}>}Amount)/COUNT({1}Count)

Re: Set Analysis little issue

alec1982 — Thu, 06 Mar 2014 17:20:09 GMT

Sorry, I changed the structure and added the formula below it o it will be clear.

Re: Set Analysis little issue

MK_QSL — Thu, 06 Mar 2014 17:23:37 GMT

Is my above answer working?

if not, let me know your logic of average. I will come back to you on this.

Re: Set Analysis little issue

alec1982 — Thu, 06 Mar 2014 17:25:15 GMT

This logic doesn't work. it gives a zigzag line which means a different value for each bar of the dimension.

Re: Set Analysis little issue

MK_QSL — Thu, 06 Mar 2014 17:35:58 GMT

Create a variable

VAR =SUM({1<Type = {'A'}>}Amount)/COUNT({1}Count)

= should be there in variable definition

Now Chart expression should be

$(var)