Set analysis for a bar chart

[maarcus_smith]

hi

i have a formula that i need to include another set analysis expression in it for 2 types of roles.

this is the current expression:

sum({<Flag={"1"}>} [Total Hours])
/
sum({<[Type]=,Flag={"1"}>} total <Week,Year> [SumOfSch Hrs])

i want to update the expression for both sums so that it only takes in values from a field named "Stream" and a value within that field named "new".

i will then put in the same expression again but this time look for a value named "old" in the field "Stream" .

i put it in the expression editor, it says expression ok but no data is coming up.

i know data is there as when i would select a value in the "stream" field in say a listbox, it changes the chart.

my expression =

sum({<Flag={"1"} ,[Stream]={'Old'}>} [Total Hours])
/
sum({<[Type]=,Flag={"1"}, [Stream]={'Old'}>} total <Week,Year> [SumOfSch Hrs])

any idea where im going wrong?

thanks 

NOTE: had to create new post as it would not allow me to reply to my first post on this topic for some reason

[rubenmarin · Accepted solution]

Hi, I would reccomend to make some test reproducing the expression step by step:

- add a table with flag and Stream as dimensions, and sum(Total Hours] as Expression

- select flag=1 and stream=Old and check if there is any result.

- add Week and Year as dimesnions

- add Sum([SumOfSch Hrs]) as expression.

- check that it aslso returns values and those values have a relation with the first expression

- add a last expression with the full expression and check what it does for every row

With all this you should se enough hints to understand whats happening, some of them should have no relation with Stream and are retuning 0.