topic urgent help on Set Analysis in QlikView

urgent help on Set Analysis

Wed, 02 Sep 2009 18:01:48 GMT

Hi,

I am writing below codes to get an expression (each month balance/first month balance. Here denominator is always constant i.e. first yr balance however, numerartor will change on balance specific to each month

With Min Yr:

((Sum({1<yymm = {'$(=Min(yymm))'}>} ((OPEN_BAL_AMT)))+Sum({1<yymm = {'$(=Min(yymm))'}>} ((CLOSE_BAL_AMT))))/2)

This code gives first month balance.

All Yrs:

(sum( {$<yymm = >} OPEN_BAL_AMT )+sum( {$<yymm = >} OPEN_BAL_AMT ))

This gives balance wrt to each month.

Now when I try to divide above two expressions, I am always getting first month balance and not other month balance.

Plz help.

Sunil

urgent help on Set Analysis

piotr_kierul — Wed, 02 Sep 2009 22:36:43 GMT

Hi!

I think you just need to add "Total" in first expression, then it will be not aggregated on months and it should work just fine:

((Sum( Total {1<yymm = {'$(=Min(yymm))'}>} ((OPEN_BAL_AMT)))+Sum(Total {1<yymm = {'$(=Min(yymm))'}>} ((CLOSE_BAL_AMT))))/2)

urgent help on Set Analysis

Thu, 03 Sep 2009 13:40:33 GMT

This does not work. Is there some other way to establish it?

urgent help on Set Analysis

piotr_kierul — Thu, 03 Sep 2009 15:28:29 GMT

I'm pretty sure it should work, take a look at attached qvw.