Multiple Set Analysis

[#1]

I am trying to pull through the sum of Cost for:

• Quadrant (field), where the field shows "Acute"
• Quadrant (field), where the field shows "Mental Health", but only where the Quadrant sub category (field) shows "Day Care" and "In Patients".

I have used a set analysis wizard to create the script below, and QV tells me that the expression is ok, but the chart is showing "no data to display".

=Money(Sum({1<Quadrant+={'Acute'},Quadrant+=P({1<Quadrant={$(=Mental Health)}>}Day Care)>}Cost))

What am I doing wrong? Grateful for any advice please

Many thanks

Claire

[whiteline · Accepted solution]

What am I doing wrong?

Using the set analysis wizard

-In set analysis you can't use the fields more than one time in a set, you've used the Quadrant twice (think of this as using two listboxes simultaniously).

-the $-sign expansion tries to evaluate the expression, but there is and error as Mental Health is used without quotes and it's not a field name.

-Day Care is also used in a wrong way, since the using of P function assumes a field name, not the value.

You could try this:

=Sum({1<Quadrant={"Acute"}>+1<Quadrant={"Mental Health"}, QuadrantSubCategory={"In Patients","Day Care"}>} Cost)

[#3]

I did try using the set analysis wizard and that's the formula it gave me, so it's obviously not all that straight forward! 

Thank you Whiteline for your advice, much appreciated!