Excluding a filter

[#1]

Hi,

I have an expression that calculates the number of sessions a consultant does, and ones where it just calculates operations performed during the week and at weekdays. I am trying to build a report that i currently produce in excel, it has merged cells at the end of the table which gives an overall number of sessions performed ignooring the week days.

How can i make this expression work...

This is my expression at the moment

SUM({<session_consultant_flag={'Yes'}>}IF( Isnull( Specialty ) , 0 , funded_equivalent ))

and i need to add this:

{$<day_of_week=>} 

to make it ignore the day of the week field in my pivot. But when i put it in it doesnt make any difference.Below is how i tried to do it...

SUM({$<day_of_week=>}  {<session_consultant_flag={'Yes'}>}IF( Isnull( Specialty ) , 0 , funded_equivalent ))

Please help

Thanks,

[Kushal_Chawda]

try this

SUM({$<day_of_week,session_consultant_flag={'Yes'}>}IF( Isnull( Specialty ) , 0 , funded_equivalent ))

[#3]

No that still makes no difference.

Kate Hunter

Senior Information Analyst

Planned Care Team

Derby Hospitals NHS Foundation Trust

Level 5

Tel: 01332 786745

Ext: 86745

Please click here<http://dw-bobj01/reportfinder/reportfinder.asp> to access all Planned Care reports, including Referrals, Theatres, Waiting list (RTT) and Cancelled Ops

If you need access to single sign on, please contact IT on 85777.

[Kushal_Chawda]

Can you share the sample app?

[#5]

I can’t due to information governance, but I have attached a spread sheet of the output, highlighted in yellow is where I need them to be merged (day of week filtered) so in sessions performed it should say 10. I have managed to get it working for the planned sessions by using = sum({$<day_of_week=>}Planned_Sessions)

But can’t get the others to work.

Thanks,

Kate Hunter

Senior Information Analyst

Planned Care Team

Derby Hospitals NHS Foundation Trust

Level 5

Tel: 01332 786745

Ext: 86745

Please click here<http://dw-bobj01/reportfinder/reportfinder.asp> to access all Planned Care reports, including Referrals, Theatres, Waiting list (RTT) and Cancelled Ops

If you need access to single sign on, please contact IT on 85777.

[jonathandienst]

Its possible that you will need to override other fields, although its just guesswork without your qvw or a representative sample.

[Kushal_Chawda]

It should work

SUM({$<session_consultant_flag={'Yes'},day_of_week=>}IF( Isnull( Specialty ) , 0 , funded_equivalent ))

If not please share the sample qvw, otherwise it will be just guess

[daniel_kusiak]

Hi, kate.hunter

Try this:

SUM({<day_of_week=,session_consultant_flag={'Yes'}>}IF( Isnull( Specialty ) , 0 , funded_equivalent ))

[jagan]

Hi,

Try this

SUM({<session_consultant_flag={'Yes'}, day_of_week=>}Alt( funded_equivalent , 0))

Regards,

Jagan.

[sasiparupudi1]

Please try

IF(Isnull(Specialty),0,SUM({$<day_of_week=,session_consultant_flag={'Yes'}>}funded_equivalent))