topic set analysis in QlikView

set analysis

Mon, 16 Apr 2012 19:56:37 GMT

Hi All,

I have a problem with my calculations.

Please take a look @ the attached and let me know how to calculat the $(previous) .

It should be the $ for the line above previous column.

Thanks

set analysis

swuehl — Mon, 16 Apr 2012 20:01:53 GMT

You could try with chart inter record functions like above():

=above( EXPRESSIONUSEDFOR$ )

This should give you the expected result (except for the first month, do you have data for Dec 2009)?

Re: set analysis

Mon, 16 Apr 2012 20:08:35 GMT

Your expression is correct and I did the same thing, but for the first month of each year it returns nothing and I donlt kow why !

Thanks

set analysis

swuehl — Mon, 16 Apr 2012 20:14:34 GMT

You just don't have an 'above' row for that row. Have you limited your rows using a selection in Year?

try

=aggr(above(total sum({1} Value)),Year,Month)

set analysis

Mon, 16 Apr 2012 20:23:52 GMT

I have the above for the first line. Please check the new attached (test.xlsx) to give the idea.

Thanks

set analysis

swuehl — Mon, 16 Apr 2012 20:53:16 GMT

Try

=sum(aggr (Above(total Sum({1} WEIGHT)/2000),Year,Month))

Re: set analysis

Tue, 17 Apr 2012 03:53:44 GMT

try using one dimension, by changing ur Year and Month dimension into MonthYear dimension..

See attached file.

Hope helps,

David Sugito

Business Intelligence Developer

Re: set analysis

Tue, 17 Apr 2012 19:42:50 GMT

Thanks David,

But it's not what I'm looking for. I don't want to create a data island and also my client wants to keep the presentation as it is.

Any idea is appreciated.

Thanks

Nima

Re: set analysis

swuehl — Tue, 17 Apr 2012 19:58:36 GMT

Have you tried what I posted above?

In your last posted file, you don't have any data prior 2010, so I am not sure where the above() value for Jan 2010 shown in your firts posted file comes from.

I have attached a demo with some data (assumed that this comes close to your situation) and with a chart demonstrating what I was talking about before.

Regards,

Stefan

Re: set analysis

Tue, 17 Apr 2012 20:21:03 GMT

Thanks Swuehl,

You are right , I had limited the year that's why I was not able to see the first month. your calcul worked perfectly.

Cheers,

Nima