
Set Analysis
Clever Anjos Aug 25, 2010 3:38 PM (in response to ajlwhite)Could you share some data examples?

Set Analysis
ajlwhite Aug 25, 2010 5:18 PM (in response to Clever Anjos )Here are some data values
e.g scenario 400 devebys = (303463758) = 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
Neil Miller Aug 25, 2010 6:07 PM (in response to ajlwhite)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])
Temp.qvw 136.0 K

Set Analysis
ajlwhite Aug 27, 2010 1:00 PM (in response to Neil Miller)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
John Witherspoon Aug 27, 2010 8:42 PM (in response to ajlwhite)
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?



