topic Re: Set Analysis Values in QlikView

Set Analysis Values

bobbydave — Tue, 06 Jan 2015 15:53:08 GMT

Hi there,

I have a value that is broken down in to 3 categories X, Y and Z. There are another of groups that will have the same categories.

I am creating a stacked bar chart and it should add the Values for each grouping. Right now, I am just concentrating on getting one group (Other) working with the correct values for Breakdown.

The value that appears that appear are totally different to what should appear.

I should be getting something like (from an excel sheet)

Group	Breakdown	Value

Other	X	74,000
Other	Y	0
Other	Z	11,000

but instead, I am getting

Group	Breakdown	Value

Other	X	962,000
Other	Y	26,000
Other	Z	143,000

My set analysis is:

sum({<Breakdown={'*X*', '*Y*', '*Z*'}, Grouping={'OTHER'}, Year={'2014'}, Month={'Jan'}>} Value)

Any chance someone knows what is wrong with this?

Help appreciated. Thanks

Re: Set Analysis Values

terezagr — Tue, 06 Jan 2015 15:58:18 GMT

Hi,

you can try :

sum({<Breakdown={"*X*", "*Y*", "*Z*"}, Grouping={'OTHER'}, Year={'2014'}, Month={'Jan'}>} Value)

Re: Set Analysis Values

Anonymous — Tue, 06 Jan 2015 16:00:46 GMT

couple of things to verify and correct as necessary:

1. Grouping is not same as Group

2. Other is not equal to OTHER.

From looking at the tables in your post, try this:

sum({<Breakdown={'*X*', '*Y*', '*Z*'}, Group={'Other'}, Year={'2014'}, Month={'Jan'}>} Value)

Re: Set Analysis Values

agomes1971 — Tue, 06 Jan 2015 16:02:43 GMT

Hi,

Sum({$<Breakdown={'"*$(=X)*"','"*$(=Y)*"','"*$(=Z)*"'},Grouping={$(=Other)},Year={$(=2014)},Month={$(=Jan)}>}value)

try this...

Re: Set Analysis Values

bobbydave — Wed, 07 Jan 2015 08:25:26 GMT

This gives a blank $0 value with nothing returned

Re: Set Analysis Values

bobbydave — Wed, 07 Jan 2015 08:26:03 GMT

That's my bad. That was a typo.

Values are being returned but just incorrect values.

Re: Set Analysis Values

bobbydave — Wed, 07 Jan 2015 08:26:19 GMT

Returns the same values that I have.

Re: Set Analysis Values

jagan — Wed, 07 Jan 2015 08:32:57 GMT

Hi,

Attach Sample file then it would be easier to give solution.

Regards,

Jagan.

Re: Set Analysis Values

rubenmarin — Wed, 07 Jan 2015 08:43:27 GMT

Hi, set analysis seems ok, probably you need to double-check the data, you can add an expression like:

count({<Breakdown={"*X*","'*Y*", "*Z*"}, Grouping={'OTHER'}, Year={'2014'}, Month={'Jan'}>} Value)

This way you can check if the number of records in QV is equal to the number of records in Excel.

A sample data or qvw would be nice to check what's going on.

Re: Set Analysis Values

bobbydave — Wed, 07 Jan 2015 14:01:39 GMT

As you can see, Table 1 has data corresponding to Graph 1.

The Graph should show a Year on Year comparison. I've all months 2014 and Jan 2015 so really, I am for now comparing Jan 2014 and Jan 2015. But as you can see from the Fast Change, the value have decimal places??

As you can see, Table 2 has data corresponding to Graph 2. They more or less are a breakdown of Table 1. Values may be different but you get the idea.

This table breaks each group down (Breakdown). Again on Fast Change, these values are incorrect.

Any help appreciated.

Re: Set Analysis Values

rubenmarin — Wed, 07 Jan 2015 14:26:46 GMT

Hi, I'm not sure to understand...If you make a comparison of one month in selected (or max) year with the same month last year, the dimension should be only the month, and you can use these excpression:

Current Year: sum({<Year={$(=Max(Year))}>} [Metric Value])

Previous Year: sum({<Year={$(=Max(Year)-1)}>} [Metric Value])

About the decimal you can set, in 'Number' tab, the integer type

Re: Set Analysis Values

bobbydave — Wed, 07 Jan 2015 16:00:45 GMT

This is more or less what I am looking for.

I am comparing Jan 2014 and Jan 2015 and there will be data for Feb 2015 in time.

The same data for the second graph; it was just broken down into segments.

Thanks