topic Set Analysis in QlikView

Set Analysis

Wed, 25 Aug 2010 19:37:02 GMT

I have been struggling with this for ever - I am relatively new to Qlikview

I have Columns Actvalue, ValueTypeID, RunTypeID, DEVEbyS, ScenarioID

I have an expression as follows: sum ( {<ValueTypeID = {"EVE"}> * <RunTypeID = {"Base"}>} [DEVEbyS] )

This gives me values by Scenario - so far so good

I have a second expression: sum ( {<ValueTypeID = {"EVE"}> * <RunTypeID = {"Base"}> * <ScenarioID = {"0"}>} [ActValue] )

This gives me a single value for scenario 0 = also so far so good

here's the problem when I divide expression 1 by expression 2 - I get no data !!

i.e. sum ( {<ValueTypeID = {"EVE"}> * <RunTypeID = {"Base"}>} [DEVEbyS] ) / sum ( {<ValueTypeID = {"EVE"}> * <RunTypeID = {"Base"}> * <ScenarioID = {"0"}>} [ActValue] )

Is the set ScenarioID = 0 being applied in tboth expressions when I divide one by the other? or have i messed up something else

Set Analysis

Clever_Anjos — Wed, 25 Aug 2010 19:38:44 GMT

Could you share some data examples?

Set Analysis

Wed, 25 Aug 2010 21:18:04 GMT

Here are some data values

e.g scenario 400 devebys = (-30346-3758) = -34104

Bas, EVE, Scenario 0 Actvalue = 188806+82605 = 271411

Answer wanted = -34104/271411 = -12.57%

Thanks

RunTypeID	ValueTypeID	ScenarioID	ActValue	DEVEbyS
Base	EVE	400	$ 158,460	$ (30,346)
Base	EVE	300	$ 165,244	$ (23,562)
Base	EVE	200	$ 172,716	$ (16,090)
Base	EVE	100	$ 180,771	$ (8,035)
Base	EVE	0	$ 188,806	$ -
Base	EVE	-100	$ 196,387	$ 7,581
Base	EVE	-200	$ 203,685	$ 14,879
Base	EVE	-300	$ 209,012	$ 20,206
Base	EVE	-400	$ 212,197	$ 23,391
Base	EVE	400	$ 78,848	$ (3,758)
Base	EVE	300	$ 79,716	$ (2,890)
Base	EVE	200	$ 80,620	$ (1,985)
Base	EVE	100	$ 81,563	$ (1,042)
Base	EVE	0	$ 82,605	$ -
Base	EVE	-100	$ 83,529	$ 924
Base	EVE	-200	$ 83,915	$ 1,309
Base	EVE	-300	$ 83,956	$ 1,350
Base	EVE	-400	$ 83,763	$ 1,158

Set Analysis

Wed, 25 Aug 2010 22:07:15 GMT

First, you don't need to use * between each Set Modifier:

sum ( {<ValueTypeID = {"EVE"},RunTypeID = {"Base"},ScenarioID = {"0"}>} [ActValue] )

I think you just need to use TOTAL <FIELD> in your Sums to group by the appropriate fields. Here's an expression that gets -12.57 for the Scenario 400s:

sum ( {<ValueTypeID = {"EVE"},
 RunTypeID = {"Base"}>} TOTAL <RunTypeID,ValueTypeID,ScenarioID> [DEVEbyS] )
/
sum ({<ValueTypeID = {"EVE"},
 RunTypeID = {"Base"},
 ScenarioID = {"0"}>} TOTAL <RunTypeID,ValueTypeID,ScenarioID> [ActValue])

EDIT: Could probably use:

sum ( {<ValueTypeID = {"EVE"},
 RunTypeID = {"Base"}>} TOTAL <ScenarioID> [DEVEbyS] )
/
sum ({<ValueTypeID = {"EVE"},
 RunTypeID = {"Base"},
 ScenarioID = {"0"}>} TOTAL [ActValue])

Set Analysis

Fri, 27 Aug 2010 17:00:15 GMT

Thanks for the reply

Firstly

I am using Version 9.00.7502.8 SR%

and the comma between set modifiers doesn't work - * seems to work as AND (Union)

Secondly - the TOTAL field solves half the problem -

i.e for the total group

Sum of Actvalue for Assets 800,000 plus Liabilities (750,000) = 50000 - NOTE: 50000 is what i want

but when I drill down to assets only - I get 800,000 - 0 = 800,000 - I still want the original 50000?

does this make sense?

Set Analysis

johnw — Sat, 28 Aug 2010 00:42:06 GMT


ajlwhite wrote:I am using Version 9.00.7502.8 SR%
and the comma between set modifiers doesn't work

Then you probably have a typo.


ajlwhite wrote:* seems to work as AND (Union)

Intersection, not union. Yes, it works, but learning the right syntax would be much more efficient, both from a coding and maintenance standpoint and from the standpoint of what QlikView has to go through to calculate your result.

If you can't get multiple set modifiers to work, how about posting the expression that isn't working?