Set analysis for a bar chart

maarcus_smith — Fri, 26 Nov 2021 08:59:40 GMT

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

Re: Set analysis for a bar chart

rubenmarin — Sat, 27 Nov 2021 08:38:40 GMT

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.