Set Analysis

[#1]

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

[Clever_Anjos]

Could you share some data examples?

[#3]

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

[#4]

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])

[#5]

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?

[johnw]

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?