topic Re: Excluding a filter in QlikView

Excluding a filter

Thu, 06 Aug 2015 09:34:48 GMT

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,

Re: Excluding a filter

Kushal_Chawda — Thu, 06 Aug 2015 09:46:15 GMT

try this

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

Re: Excluding a filter

Thu, 06 Aug 2015 09:53:02 GMT

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.

Re: Excluding a filter

Kushal_Chawda — Thu, 06 Aug 2015 09:56:17 GMT

Can you share the sample app?

Re: Excluding a filter

Thu, 06 Aug 2015 10:03:21 GMT

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.

Re: Excluding a filter

jonathandienst — Thu, 06 Aug 2015 10:03:39 GMT

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

Re: Excluding a filter

Kushal_Chawda — Thu, 06 Aug 2015 10:10:14 GMT

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

Re: Excluding a filter

daniel_kusiak — Thu, 06 Aug 2015 10:13:19 GMT

Hi, kate.hunter

Try this:

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

Re: Excluding a filter

jagan — Thu, 06 Aug 2015 10:17:06 GMT

Hi,

Try this

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

Regards,

Jagan.

Re: Excluding a filter

sasiparupudi1 — Thu, 06 Aug 2015 11:06:49 GMT

Please try

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